working - nhibernate preload collection




Is this the right way to eager load child collections in NHibernate (4)

I have used too much time to find a nice way to eager load child collections. so far this is what I got. It's working but I find it hard to believe that this is the best way to write this query

 [Fact]
    public void EagerLoadQueryOverWithFutureTest()
    {
        const long journalNr = 1470;

        var query = _repo.QueryOver().Where(s => s.JournalNr == journalNr).Future<Sag>();
        var sagsansoegning = _repo.QueryOver().Where(an => an.JournalNr == journalNr).Fetch(x => x.Sagsansoegning).Eager.Future<Sag>();
        var noter = _repo.QueryOver().Where(n => n.JournalNr == journalNr).Fetch(x => x.Noter).Eager.Future<Sag>();
        var filer = _repo.QueryOver().Where(f => f.JournalNr == journalNr).Fetch(x => x.Filer).Eager.Future<Sag>();
        var emails = _repo.QueryOver().Where(e => e.JournalNr == journalNr).Fetch(x => x.Emails).Eager.Future<Sag>();
        var journal = _repo.QueryOver().Where(j => j.JournalNr == journalNr).Fetch(x => x.Journal).Eager.Future<Sag>();
        var sagsTilstand = _repo.QueryOver().Where(t => t.JournalNr == journalNr).Fetch(x => x.Tilstand).Eager.Future<Sag>();
        var boligsocialEvalueringer = _repo.QueryOver().Where(b => b.JournalNr == journalNr).Fetch(x => x.BoligsocialEvaluering).Eager.Future<Sag>();
        var result = query.SingleOrDefault();
        result.JournalNr.Should().Be(journalNr);
        result.Emails.Should().HaveCount(c => c > 20);
        result.Filer.Should().HaveCount(c => c > 20);
    }

I concur with @RadimKöhler as soon as you eager load more than one collection then a Cartesian product always occurs. For selecting a suitable batch size then I would probably choose this to be the same as the page size as it just feels right... (no evidence why though)

There is another technique that you may feel is a better fit and that is to read this blog post by Ayende which shows you how you can send two future queries at the same time to eager load multiple collections that soul job is to load each collection singly.

However whichever route you take I suggest throwing a profiler at the results to see which performs better for you...


I would like to append different point of view, not the answer how to use "future" to make only one round-trip to DB.

Not sure why you need to fetch all the properties. I guess it could be 1) to be used by your code, e.g. to render UI or 2) as the API which somehow serializes that and passes it to the client.

In the first scenario, I would suggest to use the session per request and use the lazy load. In the second, I would suggest to introduce one step: building the DTO before passing it to client. That could be done while session is still open, therefore use the lazy load.

Lazy load in both scenarios. So, in both cases I would suggest to use different approach then explicit fetching. It will use more or less the same amount of selects and round-trips to DB. But at the end it could be more effective. Because all that job would be left on the NHibernate

The thing is to change the eager-fetch (manual) loading with the batch-size (native) loading. See more here 19.1.5. Using batch fetching. In this case, NHibernate will process your query, and then will go again with few more queries to load the remaining data. But only if really needed

The advantage is, that you are not a prisoner of your queries. You can consume any property which could/should be available on the entity, until the session is open (I.e. during View rendering or serializing). You do not have to go and append another explicit query over join to get eager data.

So, while Future could be solution to the world were your connectivity to DB from APP is very slow... it is not the world were to use NHibernate (ORM). The advantage of the lazy mapping and ad hoc loading (with power of batching) is from maintenance perspective the right way... I'd say


NHibernate iStatelessSession returns duplicate parent instances on eager fetch

I would say: Do not use StatelessSession. It does not suite to this use case.

13.2. The StatelessSession interface

Alternatively, NHibernate provides a command-oriented API that may be used for streaming data to and from the database in the form of detached objects. A IStatelessSession has no persistence context associated with it and does not provide many of the higher-level life cycle semantics. In particular, a stateless session does not implement a first-level cache nor interact with any second-level or query cache. It does not implement transactional write-behind or automatic dirty checking. Operations performed using a stateless session do not ever cascade to associated instances. Collections are ignored by a stateless session. Operations performed via a stateless session bypass NHibernate's event model and interceptors...

I just tried explain that here: NHibernate: Select one to Many Left Join - Take X latest from Parent, The problem here is, that your JOIN is resulting in this SQL result, which is not suitable for paging (which you will need sooner or later)

PARENT1 CHILD1
PARENT1 CHILD2
PARENT1 CHILD3
PARENT2 CHILD4
PARENT2 CHILD5 // if we would take 5 records, the parent2 won't get child6
PARENT2 CHILD6

So this resultset is not the way to go. I would strongly suggest: use

  • standard session, isnide of using (to let it dispose immediately)
  • load the list of root entity (Parent) and
  • let NHibernate load their children lazily - in separated SQL query.

The query could/should be like this:

ISessionFactory factory = ...;
using (var session = factory.OpenSession())
{
    var list = session.QueryOver<Parent>()
    .Skip(100)
    .Take(25)
    .List<Parent>();

    list.Last() // this will load all occupations at once
        .Childs // if batch-size is higher than page size
        .Any(); // otherwise touch more items

} // session is closed and disposed

As the above code snippet shows, to avoid 1 + N issue, we have to use one of the smart mapping features:

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...

And the parent mapping should be like:

HasMany(x => x.Childs)
    ...
    .BatchSize(100) // should be at least 25

Please, check also these:

  • How to Eager Load Associations without duplication in NHibernate?
  • NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  • Is this the right way to eager load child collections in NHibernate

NOTE: Some people could suggest to you to use Result Transforemer, as you've tried. This solution could work, but is done in C#, in memory, so all data are loaded (multi lines) and then narrowed. I would never use that. Check: Criteria.DISTINCT_ROOT_ENTITY vs Projections.distinct


NHibernate: Select one to Many Left Join - Take X latest from Parent

The way I would go here is:

  • load the list of root entity (Parent) and
  • let NHibernate load their children lazily - in separated SQL query.

To avoid 1 + N issue, we can use smart mapping 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...

So, the query should be like this:

session.QueryOver<Parent>()
    .Where(x => x.TimeStamp > from)
    .And(x => x.TimeStamp < to).OrderBy(x => x.TimeStamp).Desc
    //.Left.JoinQueryOver<Child>(x => x.Childs)
    // .TransformUsing(new DistinctRootEntityResultTransformer())
    .Skip(start) // paging
    .Take(maxCapacity)
    .List<Parent>();

And the parent mapping should be like:

<class name="Parent">
    ...
    <bag name="Childs" batch-size="3">
        ...
    </bag>
</class>

Please, check also these:

  • How to Eager Load Associations without duplication in NHibernate?
  • NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  • Is this the right way to eager load child collections in NHibernate