Duplicates when it wants to receive a link (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"); } 

Note that SecondObject refers to ThirdObject (this means the key is in 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 the database. The SQL query returns 24 rows, as expected.

However, here 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 some reason creates 24 SecondObject instead of 1.

I assume that he does not know how to match the "join fetch" (on the left or inside, it does not seem to matter) for the Reference (link to ThirdObject in SecondObject).

What are my options? I cannot change the data model, but I need to download all this.

Thanks in advance!

Answer 1

The distinctive root object only works when loading the parent and children. For grandchildren and great-grandchildren, this does not work. The problem is that you are loading several collection associations and returning a large cartesian product

Please read this article by Ayende , which explains why this is the workaround.

What may not immediately manifest itself will lead to a Cartesian product. This is stated in the documentation , but I think we can all agree that although there may be reasons for this behavior, this is far from ideal.