title: nhibernate multicriteria CTE subquery
body: <p>I currently have a MultiCriteria query, which filters the results based on the ids being within a sub query </p>
  
  <p><code>Subqueries.PropertyIn("Id", detachedCriteria)</code></p>
  
  <p>The sub query is the same for all queries used in the multicriteria query.</p>
  
  <p>It seems a bit ugly looking at the sql that the sub query is repeated, in my current case 15 times.</p>
  
  <p>The reason for the separate queries is each one has different joins, and don't want one massive Cartesian join.</p>
  
  <p>If I was writing the sql by hand I would pull out the repeated sub query into a common table expression</p>
  
  <pre><code>WITH XYZ AS
  {
      ....
  }
  </code></pre>
  
  <p>and then the sub query would be where id in XYZ in the 15 queries.</p>
  
  <p>This is a bit sql server specific, an alternative would be a temporary table, or other database specific feature.</p>
  
  <p>Any ideas of how to improve the query, or am I stuck with the sub queries being duplicated?</p>