outer-join and result set

nick72 · **Joined:** Tue Dec 20, 2005 9:40 pm **Posts:** 20

I'm not sure if this is a bug or by design, so I'm asking about it here before logging it in jira. Anyway, I have an entity with outer-join=true on a bag. In this case, my entity has three items in the bag. When I run a criteria query I get three of the same entity in my result set. I realize obviously the sql will return 3 rows, Eg.

EntityID BagItemID
1 1
1 2
1 3

But shouldn't NHib be returning just the one entity with ID 1 with a List with items BagItemID 1, 2, and 3?

Relatedly, I notice in the sql when I use a limit it's limiting on the query which does the inner join. Thus the limit is being applied to the rows returned, not the entities returned. This doesn't seem right to me.

Anyway, maybe I'll just give up using outer-join.

(I have yet to test this using projections)

So, is this how this is supposed to work?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Don't know how it works in NHibernate, but the equivalent in Hibernate is "criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)". Figure out how to set the result transformer on your criteria object.

onevextchuck · **Posted:** Fri Feb 02, 2007 12:48 pm

Thanks for the pointer tenwit. I have been looking for this for a while also and your comment pointed me in the right direction. The approach in .NET is...

Code:

criteria.SetResultTransformer(New NHibernate.Transform.DistinctRootEntityResultTransformer())

Just in case it helps, we are using 1.2 Beta 3.