While eager fetching of collections looks like a simple and quick solution to select N+1 problem, it does introduce an issue. Since eager fetching uses left outer joins, it results in Cartesian product of matching row in root table and collection table. Following unit test illustrates this behavior. We have three employees stored in-memory database. All three employees live in London. Two of the three employees are members of a community and the third employee is member of two communities. The query in the following unit test loads all employees living in London and also eagerly fetches their Communities collection. I expect to get three employee instance back.

[Test]
public void QueryOver()
{
  using (var transaction = Database.Session.BeginTransaction())
  {
    Address residentialAddress = null;
    var employees = Database.Session.QueryOver<Employee>()
            .JoinAlias(e => e.ResidentialAddress, 
            () => residentialAddress)
            .Where(() => residentialAddress.City == "London")
            .Fetch(e => e.Communities).Eager
            .List<Employee>();

    Assert.That(employees.Count, Is.EqualTo(3));
    transaction.Commit();
  }
}

If you run this test, you will note that we get four records from database and the test fails. Why did that happen? If we look at the SQL that is issued by the preceding query, which is shown next, we realize that there is a join between the Employee and Community tables:

SELECT          this_.id AS id0_2_, 
                //... employee columns 
                residentia1_.id AS id2_0_, 
                //... address columns 
                communitie4_.employee_id AS employee1_4_, 
                community5_.id           AS community2_4_, 
                //... community columns 
FROM            employee this_ 
INNER JOIN      address residentia1_ 
ON              this_.id=residentia1_.employee_id 
LEFT OUTER JOIN employee_community communitie4_ 
ON              this_.id=communitie4_.employee_id 
LEFT OUTER JOIN community community5_ 
ON              communitie4_.community_id=community5_.id 
WHERE           residentia1_.city = @p0;@p0 = 'London' [Type: String (0)]

To LC: Format the following part of the above in Code Highlight

LEFT OUTER JOIN employee_community communitie4_ 
ON              this_.id=communitie4_.employee_id 
LEFT OUTER JOIN community community5_ 
ON              communitie4_.community_id=community5_.id 

The left outer join between Employee and Community makes the employee who is member of two communities appear in the result twice. So we get a total of 4 records – 2 each for the employees who are member of one community and 2 for employee who is member of 2 communities. If we had 1000 qualifying employees and each a member of 2 communities, we would have got back 2000 results. The problem amplifies even more if we eagerly fetch more than one collection in the same query. In that case, the original Cartesian product would be multiplied by the number of the items in the new collection.

This is such a common problem that NHibernate has provided a neat fix for this. The fix is in the form of a result transformer. Result transformer operates on the results loaded from database and transforms them into a different form. A result transformer is represented by interface IResultTransformer. Various different implementations of this interface are available to satisfy different result transformation requirements. The one we are interested in here is called DistinctRootEntityResultTransformer. This implementation takes the query results which may contain duplicates due to Cartesian product, de-duplicates the root entity, and places the collection items under the correct instance of root entity. If we change the query from the previous unit test to something as follows, then the test would pass:

Address residentialAddress = null;
var employees = Database.Session.QueryOver<Employee>()
.JoinAlias(e => e.ResidentialAddress, () => residentialAddress)
.Where(() => residentialAddress.City == "London")
.Fetch(e => e.Communities).Eager
.TransformUsing(Transformers.DistinctRootEntity).List<Employee>();

We have registered a result transformer to be used with this query by placing a call to method TransformUsing just before we call the terminal method List<Employee>. The parameter passed to this method is an instance of IResultTransformer that we want to be used. We have used factory class Transformers which make easy to work with all in-built transformers. Transformers.DistinctRootEntity is what would give us the unique results.

So are we saying that DistinctRootEntityResultTransformer fixes the problem introduced by eager fetching? In a way yes, but the solution is not most elegant. Result transformers do their transformation in memory. That means we are loading all the results out of a Cartesian product into memory first and then processing them before they are returned to our code. Cartesian product has a tendency to grow too large too soon. There could be situation when a Cartesian product inadvertently returns hundreds of thousands of records which would kill your application, both memory-and-CPU wise. For these reasons it is best to avoid eager fetching if you can. What to do when we do not want collections lazily loaded because of select N+1 issue and we also do not want them to be eagerly fetched due to Cartesian product issue? Solution is to use future queries.

Address residentialAddress = null;
var employees = Database.Session.QueryOver<Employee>()
  .JoinAlias(e => e.ResidentialAddress, () => residentialAddress)
  .Where(() => residentialAddress.City == "London")
  .Future<Employee>();

Database.Session.QueryOver<Employee>()
  .JoinAlias(e => e.ResidentialAddress, () => residentialAddress)
  .Where(() => residentialAddress.City == "London")
  .Fetch(e => e.Communities).Eager
  .Future<Employee>();

Database.Session.QueryOver<Employee>()
  .JoinAlias(e => e.ResidentialAddress, () => residentialAddress)
  .Where(() => residentialAddress.City == "London")
  .Fetch(e => e.Benefits).Eager
  .Future<Employee>();

SELECT this_.*, 
       community5_.*, 
FROM   employee this_ 
       INNER JOIN address residentia1_ 
               ON this_.id = residentia1_.employee_id 
       LEFT OUTER JOIN employee_community communitie4_ 
                    ON this_.id = communitie4_.employee_id 
       LEFT OUTER JOIN community community5_ 
                    ON communitie4_.community_id = community5_.id 
WHERE  residentia1_.city = 'London';