Use Fetch in NHibernate to solve N+1

 

http://bartwullems.blogspot.com/2012/03/nhibernate-eager-fetching.html

 

NHibernate eager fetching

 

NHibernate supports the concept of eager fetching for a long time. However there are some things to consider when you start using this with the out-of-the-box NHibernate Linq provider.
Let’s first look at the obvious way:

view plain print ?

1. var customers = session  
2. .Query<Customer>()  
3. .Fetch(c => c.Orders)  
4. .ToList();  

This will return the customer and all the customer’s orders in a single SQL statement.

Rule #1: Fetch() statements must always come last.

If you want to mix Fetch with other clauses, Fetch must always come last. The following statement will throw an exception:

view plain print ?

1. var customers = session  
2. .Query<Customer>()  
3. .Fetch(c => c.Orders)  
4. .Where(c => c.CustomerId == "ABC")  
5. .ToList();  

But this will work fine:

view plain print ?

1. var customers = session  
2. .Query<Customer>()  
3. .Where(c => c.CustomerId == "ABC")  
4. .Fetch(c => c.Orders)  
5. .ToList();  

Rule #2: Don’t fetch multiple collection properties at the same time.

Be careful not to eagerly fetch multiple collection properties at the same time. The following statement will execute a Cartesian product query against the database, so the total number of rows returned will be the total Subordinates times the total orders.

view plain print ?

1. var employees = session  
2. .Query<Employee>()  
3. .Fetch(e => e.Subordinates)  
4. .Fetch(e => e.Orders)  
5. .ToList();  

Rule #3: Fetch grandchild collections using FetchMany.

You can fetch grandchild collections too.  The following statement will throw an exception:

view plain print ?

1. var customers = session  
2. .Query<Customer>()  
3. .Fetch(c => c.Orders)  
4. .Fetch(c => c.Orders.OrderLines)  
5. .ToList();  

But if you use ‘FetchMany’ and ‘ThenFetchMany’ it will work fine:

view plain print ?

1. var customers = session  
2. .Query<Customer>()  
3. .FetchMany(c => c.Orders)  
4. .ThenFetchMany(o => o.OrderLines)  
5. .ToList();  

Posted by Bart Wullems at 11:34 AM

 

Labels: .NET, Design Patterns, NHibernate