title: Order By Aggregate Subquery with NHibernate ICriteria or QueryOver
body: <p>Is there a way to achieve SQL like this with NHibernate ICriteria or QueryOver?</p>
  
  <pre><code>select *
    from [BlogPost] b
    inner join (select blogpost_id, count(*) matchCount
                  from [Tag]
                  where name in ('tag X', 'tag Y')
                  group by blogpost_id
               ) tagmatch 
    on tagmatch.blogpost_id = b.Id
    order by tagmatch.matchCount desc
  </code></pre>
  
  <p>The aim is to rank blog posts by the number of matching tags so that a post with both <em>tag X</em> and <em>tag Y</em> comes above posts with just <em>tag X</em>.</p>
  
  <p>I've got this so far:</p>
  
  <pre><code> DetachedCriteria
                  .For&lt;Tag&gt;("tag")
                  .Add(Restrictions.In(Projections.Property&lt;Tag&gt;(x =&gt; x.Name), tags.ToArray()))
                  .SetProjection(Projections.Group&lt;Tag&gt;(t =&gt; t.BlogPost))
                      .CreateCriteria("BlogPost")
                      .SetFetchMode("BlogPost", FetchMode.Eager)
                  .AddOrder(Order.Desc(Projections.RowCount()));
  </code></pre>
  
  <p>However, the resulting query doesn't join fetch <code>BlogPost</code>. Instead it returns just the ids, which leads to select n+1 when the <code>BlogPost</code>s are iterated.</p>
  
  <pre><code>public class BlogPost
  {
    ...
    ISet&lt;Tag&gt; Tags {get; set;}
  }
  
  public class Tag
  {
    BlogPost BlogPost { get; set; }
    string Name { get; set; }
  }
  </code></pre>
  
  <p><a href="https://stackoverflow.com/questions/1373539/nhibernate-2-1-left-join-on-subquery-with-alias-icriteria">This  looks like a similar issue.</a></p>
  
  <p><strong>Is this now possible with NHibernate 3?</strong></p>
  
  <p><strong>If not, is there an alternative solution?</strong></p>
  
  <p>I can change schema &amp; domain model if necessary. I don't want to use SQL or HQL if possible. </p>