Join Fetch on NH queries returns dupes

Domain:

public class Parent
{
  public ICollection<Children> Children {get;set;}
}
public class Child
{
  public string Description {get;set;}
}

Say we want to retrieve all the Parents in HQL, but we want to join fetch to get children simultaniously, so we don't lazyload children. In my case, I knew I'd be working with children immediately after retrieving Parents. The HQL would be something like this:

SELECT p FROM Parent p JOIN FETCH p.Children

Nothing fancy. When we execute that HQL query, we're going to get a Parent row returned to us, for every child for every parent. So if we've got 3 Parents, and each one has 2 children, we will have 6 rows returned from that statment.

Each row will have all of the attributes of Parent on it (as columns), and it will also have all of the attributes of child (as columns). This is how NHibernate ensures it retrieves both objects in the database simultaneously.

So, after you execute the query, NHibernate gives you a list of 6 Parents. Each parent has a duplicate of itself in the retrieved list. Why? This is because that's what was returned, for rows, when you asked for Parents. You're getting both Parent and Child, but you're only selecting the Parent as the result from the query, NHibernate is smart enough to preload each Parent's collection of Children so when you attempt to access the collection, NHibernate does not need to go back to the database to load the collection.

So, my problem was, how to get just 3 Parent's back, instead of 6. Well, a few people, might suggest using DISTINCT in your HQL, so it'd look something like:

SELECT DISTINCT p FROM Parent p JOIN FETCH p.Children

Execute that, and you'll still get 6 Parent's, each parent, along with it's dupe. Why? Because DISTINCT is a SQL based syntax, and those rows are not dupes. Only the objects that are hydrated in the object model are. So you're not going to have any luck there.

What are your options?

1.) Use the results transformer:

query/criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());

The result transformer should work after NH has retrieved the objects back from the database, and will attempt to do a comparison with your root entity (I believe on the ID) to determine if they're the same.

2.) Do it yourself in Linq with results.Distinct();

Hopefully this helps explain the situation a bit for a few people out there that might be experiencing the issue.