title: Select parent and count of children in Nhibernate using Linq in the same query
body: <p>I am trying to learn MVC and nHibernate by creating a simple blog application. </p>
  
  <p>I have a posts table and a comments table. Each post can have multiple comments. Now in my view I have to display the details of the post and the number of comments. </p>
  
  <p>I tried the below code </p>
  
  <pre><code>(from post in DbContext.Posts
  where post.ScheduledDate &lt;= DateTime.Now &amp;&amp; post.Approved == true
  orderby post.ScheduledDate descending
  select new { Post = post, CommentCount = post.Comments.Count() }).Take(10);
  </code></pre>
  
  <p>This returns the below SQL:</p>
  
  <pre><code>SELECT top 10 count(comments1_.Id) as y0_ 
  FROM Posts this_ 
  left outer join Comments comments1_ 
           on this_.Id=comments1_.PostId 
  WHERE (this_.ScheduledDate &lt;= '2009-12-29' and this_.Approved = 1) 
  ORDER BY this_.ScheduledDate desc
  </code></pre>
  
  <p>And obviously throws a sql exception that <code>group by</code> is not used. </p>