Friday, August 06, 2010

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!

22 comments:

  1. what's the difference between fecth and where then?

    ReplyDelete
  2. That looks awesome!

    Is it only NH3 or will it work for 2.1 too?

    Thanks

    ReplyDelete
  3. As you mention to be very interested in the art of programming and frequently get excited by new technology, I suggest you to take a look at this: http://aspectize.com/content/DALTechnologyHome2.aspx which allows plenty of rich scenarios you described here, with a certain elegance and ease.
    (Disclaimer: I am the co-founder of this french start-up the guy who did this stuff.)

    ReplyDelete
  4. How do you fetch with a left join as opposed to inner join programmatically like left join fetch in hql?

    ReplyDelete
  5. Hi marek, The new linq provider comes with NH3.

    ReplyDelete
  6. Matt S.3:12 pm

    This still leaves a bit to be desired. You mention not to include multiple children due to Cartesian Product it will produce.

    Are there any plans to add an option to the .Fetch extension to specify a join vs. a select? This would be similar to the old SetFetchMode method with the Criteria API.

    Also, how are grandchildren actually working in NH 3? I could never get them to work via SetFetchMode in 2.1.x.

    ReplyDelete
  7. Hi Matt,

    How is any ORM going to solve the problem of the Cartesian product when you ask it eagerly load multiple child collections? Surely that's part of the fundamental Object/Relational missmatch? What would such a select statement look like?

    I don't really understand what you're asking vis the join/select difference. The way the Fetch works currently is that it does a join with the child collection and includes the fields in the select statement.

    Grandchildren are working as described in the post.

    ReplyDelete
  8. Matt S.7:12 pm

    Hi Mike.

    I apologize for not being clearer. Currently, we can use SetFetchMode with different modes. Perhaps my understanding of FetchMode.Select is incorrect. I thought the original intent of it was to perform a second select for the child/grandchild entities in the same connection, then use the 2nd result set to populate the child/grandchild collection of the appropriate entity(ies) from the 1st select.

    select [employee_fields]
    from Employee;

    select O.[order_fields]
    from Order O inner join Employee E on O.EmployeeId = E.EmployeeId;

    select E2.[subordinate_fields]
    from Employee E1 inner join Employee E2 on E1.EmployeeId = E2.ManagerEmployeeId;


    The above 3 statements would avoid the Cartesian Product, assuming NH would use each to populate the Employee entity's 2 child collections.

    I know it truly sucks to see posts like mine when you are trying to show a cool new feature, but I am genuinely interested in better performance with NH.

    Thank you for your time!

    ReplyDelete
  9. Hi Matt,

    Sorry to be short with you earlier. I really do appreciate your comments.

    I didn't understand what you meant because I've never used the criteria API with NHibernate. I'm simply interested in the NHibernate Linq implementation. I wasn't aware that there were different fetch modes or that the criteria API produced multiple select statements for a single query. Now that you've pointed this out, I would agree that it would be very nice if NHibernate Linq did something similar.

    Sorry also to give the impression that I'm something of an NHibernate expert, this post is very much in the spirit of 'wow isn't this cool'. It sounds like you know far more about the subject than I do :)

    ReplyDelete
  10. Matt S.9:40 pm

    Mike,

    Not at all! This is a cool feature of NH 3 Linq. The magic strings used now are a nightmare.

    Also note, the current implementation of FetchMode.Select does not actually work as expected. Nor does setting a fetch mode for a path that drills down to grandchildren work now.

    There are other oddities with the Linq provider we have now with NH 2.1.2 too. For example, the expression below only pulls a single Order and a single (or null) OrderLine regardless of how many OrderLines that Order actually has.

    var order = (
    from o in Session.Linq
    .Expand("OrderLines")
    where o.Id == someIdToRetrive
    ).FirstOrDefault();

    The FirstOrDefault expression is evaluated on the entire result because OrderLines is joined with the Order instead of being loaded via a 2nd select.


    I am really hoping that these points get resolved before NH 3 is released (or soon after). Of course, I'm still very much looking forward to its release.

    Thanks for the soap box!

    ReplyDelete
  11. Hi Matt,

    The Linq provider in NH 3.0 is a complete re-write, along with a re-write of HQL, and has nothing in common with the old Linq provider. The old one sat on top of the Criteria API and has lots of limitations. The new one tranlates directly to the HQL AST and is far more powerful.

    The new Linq provider comes out-of-the-box with NH 3.0 (no more NHibernate.Linq.dll) and you access it by session.Query() rather than the old session.Linq()

    ReplyDelete
  12. Matt S.1:26 pm

    Mike,

    That does sound appealing. A direct translation to HQL is a much better choice, I think. That's great to hear! Thanks for the heads-up.

    ReplyDelete
  13. Hi,

    I'm trying to use the Fetch() on my IDictionary valued property, like this:

    var items= _session.Query().Fetch(item => item.Values).ToArray();

    Here Values is an IDictionary. I'm not sure if it is an appropriate place to post the exception message, just wanted to check if this scenario is officially supported.

    ulu

    ReplyDelete
  14. Anonymous8:57 pm

    Why fetch must be the last in query? I just don't see the logical reason behind it? Could you explain a bit?
    Also I guess the reason you have ToList() at the end of all your queries is related to the same fact.
    My concern about this is related to using Fetch with Pagination (from mvc contrib). Right now I cannot put them together because it seems that Pagination needs to get the count on the query and if you have Fetch in the query you are in trouble.

    ReplyDelete
  15. Anonymous11:28 am

    I also can't see why fetch should come last. I have my own extension method ToSortedList which does an OrderBy and then converts to a list but there's no way i can do my fetch as i can't use it before i call my extension method.

    ReplyDelete
  16. I've just recently updated to NH 3.0, and i'm having "interesting" issue. I'm not sure if it's my fluent nhibernate mapping. But I have a customer with Conversations. And each conversation has notes.
    So my code looks something like this:

    Session.Query().Where(x=>x.customernumber == customerNumber).FetchMany(x=>x.Conversations).ThenFetchMany(c=>c.Notes);

    I get the correct sql generated (no more select N+1); however, my object now has duplidate Conversations for each note in the conversation. IE, Each conversation in the list has only one note, and the list of conversations has duplicate conversations.

    Thoughts? I've tried changing my conversation mapping to AsBag(), from an IList to an ICollection, but to no avail.

    ReplyDelete
  17. That should have been Query <Customer> ()

    ReplyDelete
  18. Hi Mike,
    I don't know. TBH You're much better off asking that kind of question on the NHibernate users group.

    ReplyDelete
  19. Thanks! Will do

    ReplyDelete
  20. How would you alter that last LINQ query:

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

    ... to only get customers who had placed orders for Product ID=123 ?

    ReplyDelete
  21. Anonymous2:26 pm

    How I can fetch more than one grandchild collections?
    ReferralSource - main table
    -Phone - join table Rs as one-to-one
    - Carrier - child table for Phone
    - Type - child table for Phone
    This query gives:
    Session.Query()
    .Fetch(x => x.Phone)
    .ThenFetch(x => x.Type)
    .Fetch(x => x.Phone)
    .ThenFetch(x => x.Carrier);

    This code:

    left outer join PHONES phone3_
    on referralso0_.PhoneId = phone3_.Id
    left outer join PHONETYPES phonetype4_
    on phone3_.TypeId = phonetype4_.Id
    - duplicated join started
    left outer join PHONES phone5_
    on referralso0_.PhoneId = phone5_.Id

    ReplyDelete
  22. @IvanK - so did you find a way of fetching more than one grandchild collection without the extra join?

    ReplyDelete

Note: only a member of this blog may post a comment.