Eager fetch multiple child collections in 1 round trip with NHibernate

Oren Eini (Ayende), due to some of the comments on his various blog posts regarding EF vs NH that compare some of the different features, has finally put up a post demonstrating how to load a larger object graph in a single trip to the database. So far, all of the examples have been how to load a single collection of related entities (a one-to-many relationship), or a single related many-to-one entity, but never both at the same time.

In his latest post, he demonstrates how to load a root entity, whilst eagerly fetching TWO one-to-many collections. While NH still issues two separate queries, they are at least sent in a single trip, and honestly, the simpler SQL is both easier to understand, should you be debugging your application and running SQL traces, and the total execution time of the SQL is probably less, since there are no complicated subselects, unions, and other things going on to do the work in a single query.

However, I’m not done fighting the good fight. Ayende’s latest example still does not show how to fetch a large, complex object graph, with multiple branches coming off the root entity, and with multiple levels of collections down each branch. I’ve challenged anyone and everyone to accomplish this in NHibernate since the first “versus” posts were published. After a few timid responses suggesting I use FetchMode.Subselect (which doesn’t work) or Futures (which I haven’t seen accomplish what I’m asking for), everyone started to silently ignore the issue. When InfoQ brought up the same “versus” debate, I posted in the comments there as well, calling for anyone that could load large, complex object graphs in a single trip (hopefully in a single query), with NHibernate.

Don’t get me wrong, I’m not saying “put your money where your mouth is” to the NHibernate guys — on the contrary, I still prefer NHibernate to EF, and while I’m going to give EF4 a fair chance, I want to see NHibernate compete in this specific regard. I want to know how to do it, because I want to use it in my own code!


This entry was posted in Code and tagged , , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

11 Comments

  1. Posted January 19, 2010 at 12:53 pm | Permalink

    Sam,
    Sorry for not responding earlier, I was totally Offline this week.
    Can you send me a challenge in more concrete terms?

  2. Posted January 25, 2010 at 1:08 am | Permalink

    Basically I’m trying to figure out how to load a complete object graph in 1 round trip. It doesn’t have to be a single query, since that’s not always the best solution, but a single round trip would be nice. By “complete object graph”, I mean a single root entity, with any number of related entities and child collections, over N levels (grandchild entities, grandchild collections, etc). Also, I would like to be able to do it using something as generic as a list of strings that specify the association paths from the root entity.

    Customer c = Customer.GetByKey(1, “Orders.OrderDetails”, “BillingAddress”);

    That should fetch the Customer object with ID=1, with his Orders collection already populated, the OrderDetails objects for each Order already populated, and the customer’s BillingAddress already pouplated. You should be able to pass in any number of strings, since the method is defined with a “params string[] includes” argument.

  3. Arvind Gautam
    Posted February 26, 2010 at 2:57 pm | Permalink

    No solution to this yet?
    I’m still hunting for a way to fetch multiple levels of an object tree through NHibernate in a single roundtrip.

    Even go just so far as object->children->grandchildren

    no idea how!

  4. Posted March 8, 2010 at 1:01 pm | Permalink

    @Arvind
    I do have what I consider to be an acceptable solution for this problem. It comes down to creating a separate ICriteria for each “unique path” in my object graph, starting from the root object. So if I wanted a graph with:

    productModel.Products.Category.Products
    productMode.Products.OrderDetails.SalesOrder

    Then I would have 2 separate unique paths from my root object (productModel), and would create 2 separate ICriteria objects, and use Future to delay the actual trip to the database. This eliminates the “multiplication” problem in SQL, which would otherwise do some crazy joins, creates thousands and sometimes millions of records.

    Rather than creating the separate ICriteria objects manually, I have wrapped the functionality up nicely in a set of methods I’m using, so it looks something like:

    ProductModel model = AdvWorksDal.Instance.GetObjectById(modelID, “Products.Category.Products”, “Products.OrderDetails.SalesOrder”);

    The final code may not look exactly like that, but it will be very similar. I’ll do a specific follow up post this week or weekend, and let you know when I’ve got it. Thanks for your input!

  5. Arvind Gautam
    Posted March 9, 2010 at 1:06 pm | Permalink

    Sam!

    I’m still wondering – if you create a criteria query for a “Selecting” a list of productModel objects (by applying some restrictions…like..for ID) – and load up those product models’ Product objects by applying a “criteria.SetFetchMode(“Product”, FetchMode.Eager)” … I still don’t know how to make it load up the Category and OrderDetails of THOSE Product objects you just eager loaded :(

    If your first criteria was to the tune of …

    ICriteria criteria = Session.CreateCriteria(typeof(ProductModel));
    criteria.Add(Restrictions.Eq(“ID”, “123″));
    var objectList = criteria.SetFetchMode(“Products”, FetchMode.Eager).Future();

    I’m drawing a blank on how to form the SECOND criteria to fetch the next level – that is – Category and OrderDetails for the Products that are eager-fetched by the previous query :( Help!!!

    THanks!

  6. Posted March 9, 2010 at 1:49 pm | Permalink

    You do it like this:

    ICriteria criteria = Session.CreateCriteria(typeof(ProductModel));
    criteria.Add(Restrictions.Eq(“ID”, “123″));
    criteria.SetFetchMode(“Products”, FetchMode.Eager);
    criteria.SetFetchMode(“Products.Category”, FetchMode.Eager);
    criteria.SetFetchMode(“Products.OrderDetails”, FetchMode.Eager);
    var objectList = criteria.Future();

    However, at this point you have a “split” in your object graph. One unique path down is:
    model -> Products -> Category
    And the 2nd path is
    model -> Products -> OrderDetails.

    When you fetch all of this with a single ICriteria, you’re going to get some nasty multiplication, especially between the leafs of your graph, in this case, Category and OrderDetails, because while their parent entities are related, or are the same entities, the individual Category and OrderDetails records really aren’t directly related. But, in order to get ALL of the data you requested in a single query, you’re going to end up with a lot of rows.

    The trick is to have a separate ICriteria for each unique path down your object graph. Doing it manually would look something like this:

    ICriteria c1 = Session.CreateCriteria(typeof(ProductModel));
    c1.Add(Restrictions.Eq(“ID”, “123″));
    c1.SetFetchMode(“Products”, FetchMode.Eager);
    c1.SetFetchMode(“Products.Category“, FetchMode.Eager);
    c1.Future<ProductModel>();

    ICriteria c2 = Session.CreateCriteria(typeof(ProductModel));
    c2.Add(Restrictions.Eq(“ID”, “123″));
    c2.SetFetchMode(“Products”, FetchMode.Eager);
    c2.SetFetchMode(“Products.OrderDetails“, FetchMode.Eager);
    c2.Future<ProductModel>();

    Then you call ToList() or Future() or whatever you want. Because you already called Future() on both of the ICriteria objects, their queries will be sent to the database whenever you actually cause any query to get sent to the database. The advantage with the separate ICriteria’s is that you avoid the multiplication of rows with the leafs of your object graph (Category and OrderDetails), because 2 separate queries will be sent to the database, one for each ICriteria.

    Note that while separate queries are sent to the database, if you do a trace in SQL Server, it’s a single exec sp_executesql statement that gets sent, and the multiple queries are contained in 1 string, separated by ;’s.

    This is the approach I take with my solution, I just wrap it up in a few methods so it is easy to use. Will post later this week or this weekend, but give the above a try, and see if you get the results you want.

    Sam

  7. Arvind Gautam
    Posted March 10, 2010 at 1:32 pm | Permalink

    Sam! THANK you! You are AWESOME!

    works like a CHARM!

    though there’s another problem (which means I should’ve expanded my previous question a bit further…)

    The thing is – each level of my tree is a LIST. So basically… adding Warehouse to the previous example -
    so…

    productModel.Products.Categories.Warehouses

    so each ProductModel has multiple Products. Each Product has multiple Categories. Each Category has multiple Warehouses.

    Each time I add an eager-fetch of the subsequent layer – I start to get some sort of a cross product.

    So I’ve been able to get Products and Categories eager fetched by

    ICriteria c1 = Session.CreateCriteria(typeof(ProductModel));
    c1.Add(Restrictions.Eq(“ID”, “123″));
    c1.SetFetchMode(“Products”, FetchMode.Eager);
    c1.SetFetchMode(“Products.Categories“, FetchMode.Eager);
    c1.Future();

    Now as I add more SetFetchMode’s …

    ICriteria c1 = Session.CreateCriteria(typeof(ProductModel));
    c1.Add(Restrictions.Eq(“ID”, “123″));
    c1.SetFetchMode(“Products”, FetchMode.Eager);
    c1.SetFetchMode(“Products.Categories“, FetchMode.Eager);
    c1.SetFetchMode(“Products.Categories.Warehouses“, FetchMode.Eager);
    c1.Future();

    I start to see Products and Categories repeated :(

    I can make the root (product model) distinct through SetResultTransformer(CriteriaSpecification.DistinctRootEntity) ….
    but what do I do for subsequent levels of the tree? Ideas?

    Thanks!

  8. Posted March 11, 2010 at 12:00 pm | Permalink

    Are you mapping your collections to sets or bags? You will get the weird multiplication unless you map to sets. Some of the comments on this stackoverflow question indicate as much.

  9. Arvind Gautam
    Posted December 14, 2010 at 3:48 pm | Permalink

    Sam!

    A little more help required if you please.

    I’m trying to combine restrictions over 2 tables to produce a result set.
    Meaning – >

    in this whole scheme of ->

    User -> Blogs -> posts
    (where each is a table… each has a one-to-many relationship from left to right)

    I want a list of Users (with certain property values) who have Blogs (with certain property values) and Posts (with certain property values).

    Now if I create a DetachedCriteria for EACH object….
    how do I put them TOGETHER to query for a result?

    DetachedCriteria userC = DetachedCriteria.For();
    userC.Add(Restrictions.InsensitiveLike("Name", ));

    DetachedCriteria blogC = DetachedCriteria.For();
    blogC.Add(Restrictions.Eq("Type", ));

    DetachedCriteria postC = DetachedCriteria.For();
    postC.Add(Restrictions.Between("PostedOn", ));

    I need a result-set.. a list of users returned that has a list of blogs that has a list of posts … where the entire tree satisfies these criteria combined!
    How can I do that?

    Any help will be MUCH appreciated! Thanks!

  10. MylesRip
    Posted June 16, 2011 at 2:53 pm | Permalink

    Hi Sam,

    I have only a single branch, but it’s 3 levels deep so it’s similar to your example above using ProductModel, Products and Category. I am able to pull in the complete object graph using SetFetchMode with FetchMode.Eager for both sub-levels, but I’m getting two copies of each product. Since there is only one branch, I’m not sure why I’m getting the duplicates or how to solve it.

  11. Posted August 12, 2014 at 4:33 pm | Permalink

    I’ve been exploring for a little bit for any high quality articles
    or weblog posts on this kind of area . Exploring in Yahoo I finally stumbled upon this web site.
    Studying this info So i am satisfied to express that I’ve a very
    just right uncanny feeling I found out exactly what I needed.
    I so much unquestionably will make certain to do not omit this website and give it a look
    regularly.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>