2

I'm relatively new to using NHibernate and I'm running into a shortcoming I can't seem to work myself around. I have an object tree that I wish to retrieve from the database in a single roundtrip but end up with a cartesian product.

The objects I'm trying to retrieve are called 'AccountGroup', 'Concern', 'Advertiser' and 'Product' and I only wish to get those objects where the active user has permissions for.

My initial query looked like this:

using (var session = OpenSession())
{
    return session.Query<AccountGroupEntity>()
        .FetchMany(a => a.Planners)
        .Where(a => a.Planners.Any(p => p.Id == userId))
        .FetchMany(a => a.Concerns)
        .ThenFetchMany(c => c.Advertisers)
        .ThenFetch(a => a.Products)
        .ToList();
}

This won't work as it will return a cartesian product and the resulting entities will contain many duplicates.

However, I have NO idea how to fix this. I've seen the ToFuture() method that will allow me to execute more than one query in the same roundtrip, but I have no clue how to configure my ToFuture() query in such a way that it populates all the child collections properly.

Could anyone shine some light on how I can use ToFuture to fetch the entire tree in a single query without duplicates?

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
Toolmaker
  • 554
  • 6
  • 22
  • `ToFuture` is more used to batch *different* queries into one roundtrip. And, unfortunately, as you've discovered, `Fetch` can be unpredictable. Do you really need all of those related entities? Are there perhaps only certain fields that you need from each? – Andrew Whitaker Feb 05 '15 at 16:18

1 Answers1

2

I do have an answer to this topic, solution which I do use. But it at the end means "do not use Fetch" - do it differently. So, please, take it at least as a suggestion.

Check this Q & A:

How to Eager Load Associations without duplication in NHibernate?

Small cite:

Fetching Collections is a difficult operation. It has many side effects (as you realized, when there are fetched more collections). But even with fetching one collection, we are loading many duplicated rows.

Other words, Fetching is a fragil feature, and should be used wisely in very few scenarios, I'd say. So what to use? How to solve that?

Profit from a built in NHibernate feature:

19.1.5. Using batch fetching

NHibernate can make efficient use of batch fetching, that is, NHibernate can load several uninitialized proxies if one proxy is accessed (or collections. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can tune batch fetching: on the class and the collection level.

Batch fetching for classes/entities is easier to understand. Imagine you have the following situation at runtime: You have 25 Cat instances loaded in an ISession, each Cat has a reference to its Owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call cat.Owner on each, NHibernate will by default execute 25 SELECT statements, to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

NHibernate will now execute only three queries, the pattern is 10, 10, 5.

You may also enable batch fetching of collections. For example, if each Person has a lazy collection of Cats, and 10 persons are currently loaded in the ISesssion, iterating through all persons will generate 10 SELECTs, one for every call to person.Cats. If you enable batch fetching for the Cats collection in the mapping of Person, NHibernate can pre-fetch collections:

<class name="Person">
    <set name="Cats" batch-size="3">
        ...
    </set>

My experience, this approach is pricless. The setting working for us is batch-size="25".

If you ask for any kind of Entity (via session.Get() or .QueryOver()...) - until session is open, the first time we touch related reference or collection - it is loaded in few batches... No 1 + N SELECT Issue...

Summary: Mark all your classes, and all collection with batch-size="x" (x could be 25). That will support clean queries over root Entities - until session is open, all related stuff is loaded in few SELECTS. The x could be adjusted, for some could be much more higher...

Community
  • 1
  • 1
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • That's a bummer, I was hoping for a solution that enabled me to use a style similar to EF Include(). I'm developing a web application that uses WCF to speak to a remote server and often times the server application would retrieve data from the database and send this back to the web application. Not being able to use Fetch to pre-fetch the complete object hierarchy makes the whole ordeal a bit more complex. – Toolmaker Feb 06 '15 at 10:44
  • 1
    Hmmm, I see. EF Include does something you'd like to reproduce... As far as I now, with NHibernate the above answer is the most easiest way. We would ask for root entity (QueryOver) and then we have to touch references (many-to-one) and collections (one-to-many) we need to serialize via WCF service. These will be loaded in batches... so few SELECTS needed. I am using that approach with WEB API to serialize Entity into JSON. At the place, where serializatin is happening - session is open. All stuff is loaded as I need..and NO EXPLICIT need to "Include()". In fact it is even more hand..I'd say;) – Radim Köhler Feb 06 '15 at 10:48
  • 1
    I was thinking about doing exactly that, write a unit-of-work that fetches the data and steps through until the lowest level in the hierarchy, serialize it all and then close the session and return the serialized data set. It would be slower than fetching everything in one go but at least saves the hassles of having to map it myself. Thanks for the help. – Toolmaker Feb 06 '15 at 10:58