NHibernate N+1 fetch problem

Question

I have a entity and fluent mapping that look like this.

public class Client : EntityWithTypedId<long>
{               
    [Length(Max=50)]
    public virtual string GivenName { get; set; }

    public virtual IList<Address> Addresses { get; set; }
}

public class ClientMap : ClassMap<Client> 
{       
    public ClientMap() 
    {
        Schema("dbo");
        Table("Client");            
        Id(x => x.Id, "ClientId").GeneratedBy.Identity();           
        Map(x => x.GivenName, "GivenName");             
        HasManyToMany(x => x.Addresses)
            .FetchType.Join()
            .Cascade.AllDeleteOrphan()
            .Table("ClientAddress")
            .ParentKeyColumn("ClientId")
            .ChildKeyColumn("AddressId")
            .AsBag();
    }           
}

I then execute an ICriteria query like this

return Session.CreateCriteria<Client>()
    .CreateAlias("Organisation", "o").SetFetchMode("o", FetchMode.Join)
    .CreateAlias("Addresses", "a").SetFetchMode("a", FetchMode.Join)
    .Add(expression)
    .AddOrder(Order.Asc("Surname")).AddOrder(Order.Asc("GivenName"))
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .SetMaxResults(pageSize)
    .SetFirstResult(Pagination.FirstResult(pageIndex, pageSize))
    .Future<Client>();

Using NHProf I can see it executes a query like this which should return all client details and addresses

SELECT   top 20 this_.ClientId       as ClientId5_2_,
                this_.GivenName      as GivenName5_2_,
                addresses4_.ClientId as ClientId,
                a2_.AddressId        as AddressId,
                a2_.AddressId        as AddressId0_0_,
                a2_.Street           as Street0_0_,
                a2_.Suburb           as Suburb0_0_,
                a2_.State            as State0_0_,
                a2_.Postcode         as Postcode0_0_,
                a2_.Country          as Country0_0_,
                a2_.AddressTypeId    as AddressT7_0_0_,
                a2_.OrganisationId   as Organisa8_0_0_,
                o1_.OrganisationId   as Organisa1_11_1_,
                o1_.Description      as Descript2_11_1_,
                o1_.Code             as Code11_1_,
                o1_.TimeZone         as TimeZone11_1_
FROM     dbo.Client this_
         inner join ClientAddress addresses4_
           on this_.ClientId = addresses4_.ClientId
         inner join dbo.Address a2_
           on addresses4_.AddressId = a2_.AddressId
         inner join dbo.Organisation o1_
           on this_.OrganisationId = o1_.OrganisationId
WHERE    (o1_.Code = 'Demo' /* @p4 */
          and (this_.Surname like '%' /* @p5 */
                or (this_.HomePhone = '%' /* @p6 */
                     or this_.MobilePhone = '%' /* @p7 */)))
ORDER BY this_.Surname asc,
         this_.GivenName asc

Which returns all the records as expected

However if i then write code like

foreach(var client in clients)
{
   if (client.Addresses.Any())
   { 
       Console.WriteLn(client.Addresses.First().Street);
   }
}

I still get an N+1 issue where it does a select on each address. How can I avoid this?

Brune

Answer 1

I think you're misunderstanding what's going on here...it's almost always incorrect to use the distinct result transformer in conjunction with paging. Think about it, you're only getting the first 20 rows of a cross-product given that query above. I'm guessing that several of your clients at the end of the list aren't having their collections populated because of this, leading to your N+1 issue.

If you need to do the paging operation, consider using the batch-size hint on your collection mapping to help minimize the N+1 issue.

Note: if your typical use case is to show pages of 20 at a time, set the batch-size to this value.

Transit

Hi. When I remove the distinct result transformer the result is the same. I will add the batch size and see if it makes a difference. – Brune

Well yes, the result will obviously be the same given my above comment (since you're still only selecting the top 20 rows)...a good test would be to remove the alias as Diego has suggested AND remove the paging (while keeping the distinct transformer in place), that should give you fully populated collections... – Transit

Setting batch size seems to be the best I can do. It results in two selects, but that is better than 50. – Brune

@Craig: Honestly, this approach is probably good enough for your case, you won't be able to do this in a single trip without seriously complicating your query method - it's probably not worth it in the end... – Transit

Answer 2

When you use CreateAlias(collection), SetFetchMode(collection) has no effect.

For a better approach to eager loading of collections, see http://ayende.com/Blog/archive/2010/01/16/eagerly-loading-entity-associations-efficiently-with-nhibernate.aspx

Jeanette

Thanks for this. Do you know of an example using ICriteria rather than HQL? – Brune

Correct me if I'm wrong Diego; but this still won't help him out with the paging, will it? – Transit

Also, regarding examples of futures using ICritiera; see: ayende.com/Blog/archive/2009/04/27/nhibernate-futures.aspx - you can likely apply this to loading the collections eagerly as per the article Diego posted above. – Transit

@Transit while you can't do paging using Futures, it's easier to do the base entity query first, and then query with the join while filtering with the ids of the loaded page. Which makes you realize it's easier to just use batch-size set to your page size :-D – Jeanette

Hahaha...exactly; as I suggested above, if the typical use case is 20 per page, best to set that as the batch size and live with the two selects. Out of curiousity, why is that not settable at runtime? Would be perfect for cases like this (eg. set batch-size to page size). – Transit

There's a patch here: 216.121.112.228/browse/NH-2316. I was involved in the discussion around it, but I didn't test it. It should work. – Jeanette

Ah..nice, I knew I couldn't be the only person that ran into that one. – Transit