title: NHibernate QueryOver Subquery
body: <p>I've looked at the similar questions, but can't find a simple explanation. I could have missed it, but I promise I looked. Actually I can't even find the documentation other than a single blog post that glosses over everything rapidly and assumes you're familiar with other forms of NH.</p>
  
  <p>Given a many-to-many between <code>Program</code> and <code>Topic</code>, where the latter is in a hierarchy of <code>Topics</code>, I want to retrieve all the <code>Programs</code> for a given <code>Topic</code>, possibly including its subtopics. Since a program may be listed under multiple sub-topics of a given parent topic, I need to use a subquery or deal with having to use distinct (and the simple approach of <code>TransformUsing(Transformers.DistinctRootEntity)</code> didn't work).</p>
  
  <p>Raw SQL should be something like</p>
  
  <pre><code>SELECT ProgramId, Title, bar, baz, foo FROM Programs 
  WHERE ProgramId IN 
   (SELECT ProgramId from Program_Topics WHERE TopicId IN (1, 2, ...))
  </code></pre>
  
  <p>The results are cast into a model type for transfer to the view. My initial attempt was this:</p>
  
  <pre><code>ProgramDTO pDTO = null;
  
  /* topicIds is List&lt;int&gt; passed into function */
  
  var query = Session.QueryOver&lt;Program&gt;()
  .JoinQueryOver&lt;Topic&gt;(p =&gt; p.Topics)
  .WhereRestrictionOn(pt =&gt; pt.Id).IsInG&lt;int&gt;(topicIds)     
  .TransformUsing(Transformers.DistinctRootEntity)
  .SelectList(list =&gt; list
          .Select(program =&gt; program.Id).WithAlias(() =&gt; pDTO.Id)
          .Select(program =&gt; program.Title).WithAlias(() =&gt; pDTO.Title)
          .Select(program =&gt; program.Location).WithAlias(() =&gt; pDTO.Location)
          .Select(program =&gt; program.Description).WithAlias(() =&gt; pDTO.Description)
  )
  .TransformUsing(Transformers.AliasToBean(typeof(ProgramDTO)));
  
  return query.List&lt;ProgramDTO&gt;();    
  </code></pre>
  
  <p>Obviously this runs a join instead of a subquery, but I can't find an example of doing a subquery with a many-to-many like this. </p>
  
  <pre><code>public class Program : Entity {
      public virtual ISet&lt;Topic&gt; Topics { get; protected internal set; }
       ...
  }
  
  public class Topic : Entity {
      public virtual ISet&lt;Program&gt; Programs { get; protected internal set; }
      public virtual Topic ParentTopic { get; protected internal set; }
      ...
  }
  </code></pre>