title: Checking the Intersection of Two Collections via HQL
body: <p>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.</p>
  
  <p>Example:</p>
  
  <p>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. </p>
  
  <p>Mapping on Parent class, DocumentDefinition:</p>
  
  <pre><code> &lt;bag name="AllowedRoles" table="Many-To-Many Table" lazy="false"&gt;
        &lt;key column="ParentDefinition" /&gt; //Column from Many-To-Many Table
        &lt;many-to-many class="MyRolesClass" column="ParentRole" /&gt; //Column from Many-To-Many Table
   &lt;/bag&gt;
  </code></pre>
  
  <p>Example of what I have tried so far:</p>
  
  <pre><code>Select distinct d from DocumentDefinition d, MyRolesClass r where r in :roles and r in elements(d.Group)
  </code></pre>
  
  <p>Roles being the collection I wish to pass in.</p>
  
  <p>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.</p>
  
  <p>Hope that's clear! Cheers!</p>