title: (Fluent)NHibernate SELECT IN
body: <p>Given the following tables:</p>
  
  <pre><code>tool
   *toolid
   *n other fields
  
  process
   *processid
   *n other fields
  
  toolprocess
   *toolprocessid
   *toolid
   *processid
   *n other fields
  </code></pre>
  
  <p>When trying to select all tools for a specific process I get up to a few thousand selects on toolprocess where my Linq looks like this:</p>
  
  <pre><code>from tool in tools
  where toolprocesses.Any(t=&gt;t.Tool.Id==tool.Id)
  select tool
  </code></pre>
  
  <p>where toolprocesses contains the list of toolprocesses with the same processid</p>
  
  <p>In SQL I would just write</p>
  
  <pre><code>SELECT * FROM TOOL WHERE toolid IN 
      (SELECT TOOLID FROM TOOLPROCESS WHERE processid = 'someid');
  </code></pre>
  
  <p>It takes almost no time and works as expected</p>
  
  <p>How can I get NHibernate to create this query (or something similar)?</p>