Fun with NHibernate QueryOver and Linq provider

Given the following classes:

Blog
{
  Guid Id
  List<Post>
}

Post
{
  Guid Id
  Name
}

What's the best way to fetch a list of Blog ID's against the count of posts. Without going into HQL I've so-far got 2 ways, one using the Criteria (QueryOVer - nH3.0) and one using the NH linq provider. On a tiny database the results are quite interesting.

QueryOver needs to use a detached criteria and sub-select to get the results we need: we have to create 2 aliases, one for the select count bit of the query, and one to select over blog posts. We use a little trick with aliases to correlate the inner query to the outer query (correlated queries).

                Blog outerBlogAlias = null;
                Blog innerBlogAlias = null;

                var innerQuery =
                    (QueryOver<Blog, Blog>)session
                        .QueryOver(() => innerBlogAlias)
                        .Where(() => outerBlogAlias.Id == innerBlogAlias.Id)
                        .Inner.JoinQueryOver(o => o.Posts)
                        .ToRowCountQuery();

Did you catch the alias bit – QueryOver(() => innerBlogAlias) – the clever bit is we can reference the alias used in the outer query following to correlate the two. That’s the line which reads .Wher(() => outerBlogAlias.Id == innerBlogAlias.Id). 

                var outerQuery =
                    session.QueryOver(() => outerBlogAlias)
                    .Select(
                        list =>
                            list
                            .Select(o => o.Id)
                            .SelectSubQuery(innerQuery));

This is the outer query – look how we use .SelectSubQuery(innerQuery), to capture the count of all the children posts in the main select statement. Neat huh?

OK, wanna see the same in linq-nhibernate…

var results2 = session.Query<Blog>()
    .Select(o => new
                     {
                         Id = o.Id,
                         PostCount = o.Posts.Count
                     })
                     .ToList();

Done! And it even creates an anonymous class to hold the results. The interesting bit is the generated query. The QueryOver actually has to join Blog –> Posts. As a Post doesn’t hold a back reference to a Blog I can’t think of a better way to do this (other than changing the domain model which may or may not be acceptable). No such drama with the linq version though. It goes straight to the Posts table.

The lack of the extra join in a table with ooo, 8 posts, means that the QueryOver takes 61% of the query time (in sql), whilst the linq version only sucks up 39% of the overall time. Sql’s query plan for the QueryOver version shows that Sql Serve has to perform more work.

So what’s my conclusion – it’s that linq-nh, and queryover, are both really cool powerful query mechanisms. Each can do things the other can’t do, and you should not discount either of them up-front. Use the right query tool for the right job… And of course I should take my own advice and learn HQL as-well! For the record the reason I haven’t is that both QueryOver and Linq-NH are type-safe which is something I really like.