Good behavior for energetic loading of several siblings and grandchildren (cousins?) In NHibernate 3.0 Linq

Question

I am trying to do the following with the LINQ NHibernate 3.0 interface. I want to query an object (using the Where clause) and load some children and grandchildren. I am currently doing it like this:

var results = session.Query<Thing>() .Where(...) .Fetch(x => x.SubThingA) .ThenFetch(st => st.SubSubThingA) .Fetch(x => x.SubThingB) .ThenFetch(st => st.SubSubThingB) // etc... 

However, this leads to a Cartesian product between all grandchildren (each row of results contains many, many columns). This is discussed here "ayende" here . On the other hand, I get one round-the-world route, as opposed to splitting a request and combining it.

How can I do it better (SQL and performance), but using the NHibernate LINQ interface?

(First, I noticed that currently ToFuture methods do not work when using Fetch)

Thanks a lot!

Answer 1

Although Diego's answer is an accepted method for performing these actions at NHibernate, I really do not feel comfortable with this approach. I do not want to define explicit contracts for my objects just because I may need to get them in certain ways. Also, I don't always want to serialize them at all. Also, in many cases, I know that the best performance will always be one round trip to get all the data.

As a result, I decided to use a function that takes a list of (like safe) expressions for the root object, for example

 x => x.Child.GrandChild1 x => x.Child.GrandChild2Collection.SubInclude(c => c.GreatGrandChild) 

Where SubInclude is an extension method for IEnumerable that is used to parse these expressions.

I analyze this list of expressions and build for each subpath of each expression (x, x.Child, x.Child.GrandChild1) a request for NHibernate criteria for the root type:

 var queryOver = session.QueryOver<T>().Where( ...expression to select root objects... ); for every subpath in the current expression: queryOver.RootCriteria.SetFetchMode(subPath, FetchMode.Eager) queryOver.RootCriteria .SetResultTransformer(new DistinctRootEntityResultTransformer()) queryOver.Future() 

This is repeated for each expression in the list. The last line ensures that this desired selection will be included in everything that happens next. Then I make the actual request on the root T object, and the session automatically executes in the same reverse direction all the requests necessary to get each of the paths that I passed in the expressions.

Queries are performed separately for each expression path, so there is no problem with the Cartesian product.

The bottom line is that this is not an easy feat. There is too much code for me to publish as is. I prefer the EF4.1 Include (expression) API, which does it all automatically.

Answer 2

In most cases, you will get better performance by using batch-size in entities and collections instead of creating a mega request.

The best scenario is a query by identifier for each entity type.

---

Let's say you have a root object Client , in which there is a collection of Orders , in which there is a collection of OrderItems , a link to which is Products , and for all batch-size properties it is set to 1000 .

Let's say you get a list of 10 customers who have an average of 10 orders for 10 products each:

 var results = session.Query<Customer>().Where(...).Take(10).ToList(); 

• The first request will be used only for customers.
• When you start the iteration of the first customer.Orders collection, one query will be used to load all of them (for all clients).
• When you start iterating the first order.OrderItems collection, one request will be used to load all of them (for all orders and all customers).
• When you read a property from the first product, a single request will be used to download all of them.

So, you have only 4 requests , without any connections, all of which are invoked by PK. It is easy and effective.