Eager Fetching with nhibernate Criteria API

Question

I am trying to use the criteria Api over multiple tables with eager loading.

My stripped-down Entities look like this:

class Limit
{
    Risk {get; set;}
}

class Risk
{
   List<Company> Companies { get;set;}
}

class Company
{
  List<Address> OldAdresses {get;set;}
}

class Address
{
  string Street { get;set;}
}

My Criteria call looks like this:

var CriterionGruppe = Expression.Eq("Account.Id", someGuid);

var temp = _transaktion.Session.CreateCriteria(typeof(Limit))
.SetFetchMode("Risk", FetchMode.Eager)
.SetFetchMode("Risk.Companies", FetchMode.Eager)
.Add(CriterionGruppe)
.SetResultTransformer(new DistinctRootEntityResultTransformer())
.List<Limit>();

The addresses are still loaded with many Selects. How can I include the old adresses of a Company in my criteria call.

I already read a blog entry in ayende's blog and several other question here at stackoverflow. But still had no luck.

I hope someone could point me in the right direction.

Thanks in advance peter

When must we use eager loading in NHibernate? What is it's usage?

NHibernate Eager Fetching Over Multiple Levels

Ayende Blog

Answer 1

var account = _transaktion.Session.Load<Account>(someGuid);
var temp = _transaktion.Session.CreateCriteria(typeof(Limit))
    .SetFetchMode("Risk", FetchMode.Eager)
    .SetFetchMode("Risk.Companies", FetchMode.Eager)
    .SetFetchMode("Company.OldAddresses", FetchMode.Eager)
    .Add(Expression.Eq("Account", account))
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .List<Limit>();

However this is very inefficient. You are loading a massive amount of duplicate data to make 1 sql query. A better approach would be

1. load a projection of what is actually needed
2. use Futures and Batched lazy loading to avoid a single cartesian result set and select n+1.