Checking the intersection of two collections through HQL

Question

I have a class that has a collection displayed as a bag in my nHibernate mapping file for this class, and I want to return all instances of this class whose collection has one or more objects that I pass.

Example:

My parent class is called DocumentDefinition. It has a set of roles, which is an nHibernate object that can be accessed by a document. The two are connected using the Many-To-Many mapping. I want to pass a collection of roles request and return all instances of DocumentDefinition that have one or more of these roles.

Mapping over the 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> 

An 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 are the collection I want to go to.

So, how do I execute the query to return DocumentDefinitions, where r (Roles Class) is in both parameter lists passed to and in the collection of the DocumentDefinition object.

Hope that is clear! Hooray!

Answer 1

You were very close ... the request 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 name of the collection that does not match the mapping you display.

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