Cannot use collections with InExpression

Question

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....

What I've got object-wise is:

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; }
}

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)

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?

Answer 1

You need to

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

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.

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.