Checking the Intersection of Two Collections via HQL

Question

I have an class which has a collection, mapped as a bag in my nHibernate mapping file for that class, and I wish to return all instances of that class whose collection has a one or more of the objects which I pass in.

Example:

My Parent Class is called DocumentDefinition. It has a collection of Roles, which is a nHibernate entity, that the document can be accessed by. These two are connected via a Many-To-Many mapping. I want to pass the query a collection of roles and return all DocumentDefinition instances which have one or more of one of those roles passed in.

Mapping on Parent class, DocumentDefinition:

 <bag name="AllowedRoles" table="Many-To-Many Table" lazy="false">
      <key column="ParentDefinition" /> //Column from Many-To-Many Table
      <many-to-many class="MyRolesClass" column="ParentRole" /> //Column from Many-To-Many Table
 </bag>

Example of what I have tried so far:

Select distinct d from DocumentDefinition d, MyRolesClass r where r in :roles and r in elements(d.Group)

Roles being the collection I wish to pass in.

So how do I do a query to return DocumentDefinitions where r (Roles Class) is in both the parameter list passed in and the collection on the DocumentDefinition object.

Hope that's clear! Cheers!

Answer 1

You were very close... the query should be:

select distinct d
from DocumentDefinition d, MyRolesClass r
where r in (:roles) and r in elements(d.AllowedRoles)

And you send the desired roles using .SetParameterList("roles", collectionOfRoles).

BTW, I changed the collection name which didn't match the mapping you posted.

One more thing to consider: lazy="false" is almost always a bad idea for collections.