Select parent and count of children in Nhibernate using Linq in the same query

Question

I am trying to learn MVC and nHibernate by creating a simple blog application.

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.

I tried the below code

(from post in DbContext.Posts
where post.ScheduledDate <= DateTime.Now && post.Approved == true
orderby post.ScheduledDate descending
select new { Post = post, CommentCount = post.Comments.Count() }).Take(10);

This returns the below SQL:

SELECT top 10 count(comments1_.Id) as y0_ 
FROM Posts this_ 
left outer join Comments comments1_ 
         on this_.Id=comments1_.PostId 
WHERE (this_.ScheduledDate <= '2009-12-29' and this_.Approved = 1) 
ORDER BY this_.ScheduledDate desc

And obviously throws a sql exception that group by is not used.

Answer 1

That generated sql does look wired, i've translaated it into lambda style. Try it. If you don't have LinqPad go grab it and check it out will ease up your life alot.

  DbContext.Posts
    .Where(p=>p.ScheduledDate<=DateTime.Now && p.Approved)
    .OrderByDescending(p=>p.ScheduledDate)
    .Select(p=> new{ post= p,CommentCount = p.Comments.Count()})
    .Take(10);

Answer 2

Looks like this was some weird issue with the NHContrib LINQ provider. I upgrade to version 3 of nHibernate and the new LINQ provider seems to be working perfectly well for the same query.