nHibernate - eager fetching a list with child lists already populated

Question

I have some objects:

Public Class Person() {
    public int Id {get;set;}
    public IList<Account> Accounts {get;set;}
    public string Email {get; set;}
}

public class Account(){
    public int Id {get;set;}
    public IList<AccountPayment> Payments {get;set;}
    public IList<Venue> Venues {get;set;}
}

public class AccountPayment(){
    public int Id {get;set;}
    public DateTime PaymentDate {get;set;}
    public decimal PaymentAmount {get;set;}
}

public class Venue(){
    public int Id {get;set;}
    public string AddressLine1 {get;set;}
    public string Postcode {get;set;}
}

These classes are mapped to MS Sql with nHibernate - there is a table in the db per class...

I want to create a method in my repository, GetAccounts(int PersonID), that will return a List with all the account's child collections populated in the most efficient way. Can anyone give me any pointers on how to do this - I do not really want to set up the lists as subselects in my mappings if I can help it...

Thanks.

This blog post explains an efficient way to do that: http://ayende.com/Blog/archive/2010/01/16/eagerly-loading-entity-associations-efficiently-with-nhibernate.aspx — Diego Mijelshon, May 12 '10 at 13:58
Hi Diego, The problem is I am trying to eager load the child collections, of the Account Property of the Person object - I looked at this article before when trying to do this, and on a second investigation, I still can't see a way to do this. Am I missing something? — Paul, May 12 '10 at 15:31
This link no longer works, and unfortunately the answer doesn't include any of the relevant information from it. — Aaronaught, Oct 12 '11 at 02:31
@Aaronaught, the link works fine, although it now redirects to http://ayende.com/blog/4367/eagerly-loading-entity-associations-efficiently-with-nhibernate — Diego Mijelshon, Oct 12 '11 at 10:51
Wasn't working when I clicked on it last night. The fact remains that [naked links are not good answers](http://meta.stackexchange.com/q/8259). — Aaronaught, Oct 12 '11 at 14:11
@Aaronaught: that's a (mostly accepted) opinion/guideline, not a **fact**. A fact is 1+1=2. In this case, the article provided looked like a good fit, and I didn't feel like copy&pasting it. — Diego Mijelshon, Oct 12 '11 at 14:51

score 1 · Answer 1 · answered May 12 '10 at 14:24

1

If you have mapped your classes to tables the way you mention, why don't you just call the Person object to get all their accounts? When you call the Person object from your repository, you can eager load Accounts. Like so:

 public Person GetById(int id)
    {
        using (var tx = _sessionBuilder.GetSession().BeginTransaction())
        {
            // -- Lazy load way --
            //Person person = _sessionBuilder.GetSession().Get<Person>(id);
            //tx.Commit();
            //return person;

            // -- Eager load way --                
            Person person = _sessionBuilder.GetSession().CreateCriteria<Person>()
                   .Add(Restrictions.IdEq(id))
                   .SetFetchMode("Accounts", FetchMode.Eager)
                   .UniqueResult<Person>();
            tx.Commit();
            return person;
        }
    }

answered May 12 '10 at 14:24

LordHits

5,054
3
38
51

Ideally I would like to eager load the collections on the Accounts property of the person object (Account payment and Venue) too. I don't think the above can achieve this? – Paul May 12 '10 at 15:03
SetFetchMode for collections (especially when there is more than one) it not a good idea performance-wise. – Diego Mijelshon May 12 '10 at 15:14
@Paul - I'm unsure if FetchMode.Eager will do nested collections. You could use NHProf to see the SQL. Another way to eager load that may be to call your repository in your original statement and call Payments and Venues eagerly. .... .SetFetchMode("Payments", FetchMode.Eager) .SetFetchMode("Venues", FetchMode.Eager) .... – LordHits May 12 '10 at 15:55
@Diego - Performance is the last thing i worry about. :). I'm not sure if Paul's domain set is returning 50 records or 5 million. I'm eager to hear other solutions too. The solution i presented works for me and the application I worked on. – LordHits May 12 '10 at 16:00
Hi, I am using NHProf... It's brilliant. Unfortunately FetchMode.Eager is not having the desired results - I get a massive Cartesian product... starting to think perhaps this can't be done. The annoying thing is, if I set Fetch.Subselect() on the mapping, it's doing exactly what I'm after. – Paul May 12 '10 at 16:24

score 0 · Accepted Answer · edited May 23 '17 at 11:48

Ok, after trying to do this in many different ways, I eventually found that the most efficient solution for me outlined at this question:

Eager loading child collection with NHibernate

My question above was a vastly simplified version of the actual challenge I had, but using the above method managed to get the db hits down to 2... a huge improvement from my initial implementation.

Thanks for your help and pointers guys. Learned a bit along the way...

nHibernate - eager fetching a list with child lists already populated

2 Answers2