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

Question

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);
    }

Copyboy

Are those all one-to-many relationships? – Private

Two of them are many to many, the others are one to many – Copyboy

Answer 1

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

Bharat

Thanks for your answer, I think like you - that it's always a good idea to step back and look at what needed. I don't need to load all those properties at once it's was a test. I will take your advice and fine tune the batch size, I think that it's an easy way to improve performance. – Copyboy

Coool. And also, please, think about the great features like Projections, Subqueries (to filter one-to-many)... and Transform that into some DTO... NHibernate is very powerful the more you know... the more you can get ;) – Southwards