NHibernate Linq Eager Fetching

The new NHibernate Linq provider provides eager fetching out of the box. Here’s how you do it:

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

Note, Query<T> is an extension method in the NHibernate.Linq namespace.

The statement above will cause the following SQL to be executed:

select customer0_.CustomerId   as CustomerId0_0_,
       orders1_.OrderId        as OrderId3_1_,
       customer0_.CompanyName  as CompanyN2_0_0_,
       customer0_.ContactName  as ContactN3_0_0_,
       customer0_.ContactTitle as ContactT4_0_0_,
       customer0_.Address      as Address0_0_,
       customer0_.City         as City0_0_,
       customer0_.Region       as Region0_0_,
       customer0_.PostalCode   as PostalCode0_0_,
       customer0_.Country      as Country0_0_,
       customer0_.Phone        as Phone0_0_,
       customer0_.Fax          as Fax0_0_,
       orders1_.CustomerId     as CustomerId3_1_,
       orders1_.EmployeeId     as EmployeeId3_1_,
       orders1_.OrderDate      as OrderDate3_1_,
       orders1_.RequiredDate   as Required5_3_1_,
       orders1_.ShippedDate    as ShippedD6_3_1_,
       orders1_.ShipVia        as ShipVia3_1_,
       orders1_.Freight        as Freight3_1_,
       orders1_.ShipName       as ShipName3_1_,
       orders1_.ShipAddress    as ShipAdd10_3_1_,
       orders1_.ShipCity       as ShipCity3_1_,
       orders1_.ShipRegion     as ShipRegion3_1_,
       orders1_.ShipPostalCode as ShipPos13_3_1_,
       orders1_.ShipCountry    as ShipCou14_3_1_,
       orders1_.CustomerId     as CustomerId0__,
       orders1_.OrderId        as OrderId0__
from   Customers customer0_
       left outer join Orders orders1_
         on customer0_.CustomerId = orders1_.CustomerId

As you can see a single statement returns the customer and all the customer’s orders, just as expected.

Note that if you want to mix Fetch with other clauses, Fetch must always come last. So for example:

var customers = session.Query<Customer>().Fetch(c => c.Orders).Where(c => c.CustomerId == "ANATR").ToList();

Will throw a nasty parse exception:

Test 'NHibernate.Test.Linq.EagerLoadTests.WhereWorksWithFetch' failed: System.NotSupportedException : Specified method is not supported.

But this will work fine:

var customers = session.Query<Customer>().Where(c => c.CustomerId == "ANATR").Fetch(c => c.Orders).ToList();

Be careful not to eagerly fetch multiple collection properties at the same time. Although this statement will work fine:

var employees = session.Query<Employee>()
    .Fetch(e => e.Subordinates)
    .Fetch(e => e.Orders).ToList();

It executes a Cartesian product query against the database, so the total number of rows returned will be the total Subordinates times the total orders. Ayende discusses this behaviour here.

You can fetch grandchild collections too. Here we use ‘FetchMany’ and ‘ThenFetchMany’:

var customers = session.Query<Customer>()
    .FetchMany(c => c.Orders)
    .ThenFetchMany(o => o.OrderLines).ToList();

Which produces the following SQL:

select customer0_.CustomerId    as CustomerId0_0_,
       orders1_.OrderId         as OrderId3_1_,
       orderlines2_.OrderLineId as OrderLin1_4_2_,
       customer0_.CompanyName   as CompanyN2_0_0_,
       customer0_.ContactName   as ContactN3_0_0_,
       customer0_.ContactTitle  as ContactT4_0_0_,
       customer0_.Address       as Address0_0_,
       customer0_.City          as City0_0_,
       customer0_.Region        as Region0_0_,
       customer0_.PostalCode    as PostalCode0_0_,
       customer0_.Country       as Country0_0_,
       customer0_.Phone         as Phone0_0_,
       customer0_.Fax           as Fax0_0_,
       orders1_.CustomerId      as CustomerId3_1_,
       orders1_.EmployeeId      as EmployeeId3_1_,
       orders1_.OrderDate       as OrderDate3_1_,
       orders1_.RequiredDate    as Required5_3_1_,
       orders1_.ShippedDate     as ShippedD6_3_1_,
       orders1_.ShipVia         as ShipVia3_1_,
       orders1_.Freight         as Freight3_1_,
       orders1_.ShipName        as ShipName3_1_,
       orders1_.ShipAddress     as ShipAdd10_3_1_,
       orders1_.ShipCity        as ShipCity3_1_,
       orders1_.ShipRegion      as ShipRegion3_1_,
       orders1_.ShipPostalCode  as ShipPos13_3_1_,
       orders1_.ShipCountry     as ShipCou14_3_1_,
       orders1_.CustomerId      as CustomerId0__,
       orders1_.OrderId         as OrderId0__,
       orderlines2_.OrderId     as OrderId4_2_,
       orderlines2_.ProductId   as ProductId4_2_,
       orderlines2_.UnitPrice   as UnitPrice4_2_,
       orderlines2_.Quantity    as Quantity4_2_,
       orderlines2_.Discount    as Discount4_2_,
       orderlines2_.OrderId     as OrderId1__,
       orderlines2_.OrderLineId as OrderLin1_1__
from   Customers customer0_
       left outer join Orders orders1_
         on customer0_.CustomerId = orders1_.CustomerId
       left outer join OrderLines orderlines2_
         on orders1_.OrderId = orderlines2_.OrderId

Once again, exactly as expected.

Happy Fetching!