Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

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.

Comments

hazzik said:

PostgreSQL supports futures to.

# July 18, 2012 5:59 PM

Ricardo Peres said:

@Hazzik:

Thanks for the info! I noticed on the JIRA that MySQL is also supported.

# July 19, 2012 4:06 AM

Bevan Arps said:

Check out NHibernate's batch queries - a feature introduced by Ayende some years back.

These allow you to send multiple queries to the database in a single round trip, eliminating the downside to the technique you've described.

I've used this to load as many as six related associations at once with excellent performance (special case, long story).

# July 19, 2012 2:47 PM

Ricardo Peres said:

@Bevan Arps:

Thanks!

# July 19, 2012 3:03 PM

i need help with a resume said:

Check out NHibernate's batch queries - a feature introduced by Ayende some years back.

These allow you to send multiple queries to the database in a single round trip, eliminating the downside to the technique you've described.

# October 26, 2012 3:04 AM