Duplicates when eager fetching a reference (many-to-one)

Question

First of all, yes, I am using DistinctRootEntityResultTransformer.

I have the following (Fluent NHibernate) mapping:

public FirstObjectMap() 
{
    Id(x => x.Id):
    HasMany<SecondObject>(x => x.SecondItems).KeyColumn("FirstObject_ID");
}

public SecondObjectMap()
{
    Id(x => x.Id).Column("ID");
    References(x => x.ThirdObject).Column("ThirdObject_ID");
}

public ThirdObjectMap()
{
    Id(x => x.Id).Column("ID");
    HasMany<D>(x => x.FourthItems).KeyColumn("ThirdObject_ID");
}

public FourthObjectMap()
{
    Id(x => x.Id).Column("ID");
}

Notice, that SecondObject refers to ThirdObject (meaning the key is on SecondObject).

My query looks like this:

var query = session.CreateQuery("select distinct first from " + 
   "FirstObject as first " +
   "left join fetch first.SecondItems as second " +
   "left join fetch second.ThirdObject as third " + 
   "left join fetch third.FourthItems as four where ...");

// This is not even needed as I'm using distinct in HQL
query.SetResultTransformer(new DistinctRootEntityResultTransformer());

var results = query.List<ReinsurableObject>();

For testing, I have 1 FirstObject, 1 SecondObject, 1 ThirdObject and 24 FourthObjects in database. The SQL query returns 24 rows as espected.

However, here's the catch: NHibernate creates:

1 FirstObject
  24 SecondObject (should be 1)
     24 x 1 ThirdObject (should be 1)
         24 x 1 x 24 FourthObject (should be 24)

So NH for whatever reason creates 24 SecondObject instead of 1.

I'm guessing it doesn't know how to map "join fetch" (left or inner doesn't seem to matter) to Reference (the reference to ThirdObject in SecondObject).

What are my options ? I can't change the data model, but I do need to eager load it all.

Thanks in advance!

Barthelemy

Answer 1

Distinct root entity only works if you load a parent and children. For grandchildren and great grandchildren this does not work. The problem is you are loading multiple collection associations and returning a large cartesian product

Please read this article by Ayende which explains why this is the case and a workaround.

Something that may not be apparent immediately is going to result in a Cartesian product. This is pointed out in the documentation, but I think that we can all agree that while there may be reasons for this behavior, it is far from ideal.

Grossman

As far as I know, Cartesian product only occurs when joining parallel associations. For example, if A has B1 and B2 then that would result in Cartesian product. Ayende shows in his blog exacly that situation (from Blog b left join fetch b.Posts left join fetch b.Users). But if A, B, C, D are hierarchical (like I have), they shouldn't create a Cartesian product. The problem that I'm facing is that I have many-to-one, many-to-one, one-to-many, many-to-one hierarchy. Right ? – Barthelemy

No, I dont believe this is right you are loading many -> one -> many -> one. Look at the SQL generated and run this against the database to see what is happening – Grossman

Its the 3rd to 4th relationship which is causing the multiple collection associations hence creating 24 second objects – Grossman

Okey, I thought it was the second to third.. How do you you suggest to fix this ? I can't see how .Future would help here.. – Barthelemy

Try lazy loading instead and set a highish batch-size i.e. 50 – Grossman

Yes, but I have to load like 10k - 30k root objects (FirstObjects). Would it work ? – Barthelemy

This seems an awful lot of objects that are in the first level cache! 30k / 50 = 600 trips to DB! I really think you need to work out why you need this much data in the first place! – Grossman