NHibernate Pitfalls: Eager Loading Multiple Collections

Updated on July 19. Thanks, Hazzik!

This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

If you want to load multiple joined collections simultaneously eagerly you will end up with a cartesian product. This is mentioned in the documentation and will occur with both the LINQ, HQL, Criteria or QueryOver providers. The problem is caused by joining multiple records from the root table + the first collection table with each of the records from the second collection table.

There are two ways around it:

• Either specify a distinct root transformer that will only return a single instance of each of the root entities – keep in mind that this is done client-side, that is, the query still returns a cross join;
• Issue two future queries and have NHibernate create the objects in memory; this will send two queries, but less data will be returned:

   1: var futureBlogs = session.CreateQuery("from Blog b left join fetch b.Posts where b.Id = :id")

   2: .SetParameter("id", 1)

   3: .Future<Blog>();

   4:  

   5: session.CreateQuery("from Blog b left join fetch b.Users where b.Id = :id1")

   6: .SetParameter("id1", 1)

   7: .Future<Blog>();

   8:  

   9: var blogs = futureBlogs.ToList();

This example I got from Ayende’s blog, where you can find an explanation for this problem.

This works because of the following:

1. The first query loads records from the Blog table + the related records from the Post table;
2. The second query loads records from the Blog table + the related records from the User table; because the Blog entities are already constructed in the session (first level cache), the second query will simply attach the loaded Users collections to their related Blog instances; the results are discarded, because everything will be accessible from the Blog collection from the first query;
3. By calling ToList() we force the execution of the two future queries at the same time and get their results.

Mind you, I believe future queries only work with SQL Server, MySQL and PostgreSQL at the moment - please let me know if others are supported.