不能将集合与InExpression一起使用 [英] Cannot use collections with InExpression

问题描述

我只是深入研究了NHibernate，我遇到了我不得不编写的多个复杂"(对我来说！)查询.情况是:

I've just delved into a bit of NHibernate and I'm having trouble with one of the more 'complex' (to me!) queries I have to write. The scenario is:

我有一个职员"对象，上面附加了技能"的集合.我想传递一个技能"列表以进行查询(例如，如果我只希望可以使用库克"或代码"或两者兼而有之的人)，并返回匹配的员工列表，但我正在有点麻烦....

I've got a 'Staff' object which has a collection of 'Skills' attached. I'd like to pass in a list of 'Skills' to query against (e.g. if I only want people that can either 'Cook' or 'Code', or both) and return a list of matching Staff, but I'm having a little trouble....

我明智地做到了:

public class StaffMember : Resource
{
    public virtual string EmployeeId { get; set; }      

    public virtual bool IsTeamLeader { get; set; }

    public virtual StaffMember TeamLeader { get; set; }

    public virtual IList<Skill> Skills { get; set; }
}

public class Skill : BaseDomainObject
{
    public virtual string Name { get; set; }
}

我猜想SQL会像这样:

And I guess the SQL would go something like:

select distinct st.*
from staff st, resource re
inner join staffskills sks on re.id = sks.staffresourceid
inner join skill ski on ski.id = sks.skillid
where st.resourceid = re.id
and ski.id in (1,2,3,4)

我尝试在条件中使用"Expression.InG("Skills"，SkillsSearchList)，但是当两个集合在玩时不能使用(例如，如果他们只有一项技能，那就没问题了！ )...任何指针?

I tried to use "Expression.InG("Skills", skillsSearchList)" in the criteria, but that can't be used when two collections are in play (e.g. if they only had one skill, it would be fine!)... any pointers?

推荐答案

您需要

.CreateAlias("Skills", "sks")
.Add(Restrictions.In("sks.id", skillIdList))

我不确定是否可以使用一系列技能对象来完成.无论哪种方式，sql都将与上述相同.

I'm not sure if this can be done with a list of skill objects. Either way, the sql is going to end up the same as above.

请注意，这将创建笛卡尔乘积，因此您可能要使用一个现存的子查询或.SetResultTransformer(new DistinctRootEntityResultTransformer())来获取不同的Staff列表.

Note that this will create a Cartesian product so you might want to use an exists subquery or .SetResultTransformer(new DistinctRootEntityResultTransformer()) to get back a list of distinct Staff.

这篇关于不能将集合与InExpression一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！