Fighting cartesian product (x-join) when using NHibernate 3.0.0

by

Ok, I wrote an example for myself reflecting your structure and this should work:

int projectId = 1; // replace that with the id you want
// required for the joins in QueryOver
Project pAlias = null;
Partner paAlias = null;
PartnerCosts pcAlias = null;
Address aAlias = null;
Money mAlias = null;

// Query to load the desired project and nothing else    
var projects = repo.Session.QueryOver<Project>(() => pAlias)
    .Where(p => p.Id == projectId)
    .Future<Project>();

// Query to load the Partners with the Costs (and the Money)
var partners = repo.Session.QueryOver<Partner>(() => paAlias)
    .JoinAlias(p => p.Project, () => pAlias)
    .Left.JoinAlias(() => paAlias.Costs, () => pcAlias)
    .JoinAlias(() => pcAlias.Money, () => mAlias)
    .Where(() => pAlias.Id == projectId)
    .Future<Partner>();

// Query to load the Partners with the Addresses
var partners2 = repo.Session.QueryOver<Partner>(() => paAlias)
    .JoinAlias(o => o.Project, () => pAlias)
    .Left.JoinAlias(() => paAlias.Addresses, () => aAlias)
    .Where(() => pAlias.Id == projectId)
    .Future<Partner>();

// when this is executed, the three queries are executed in one roundtrip
var list = projects.ToList();
Project project = list.FirstOrDefault();

My classes had different names but reflected the exact same structure. I replaced the names and I hope there are no typos.

Explanation:

The aliases are required for the joins. I defined three queries to load the Project you want, the Partners with their Costs and the Partners with their Addresses. By using the .Futures() I basically tell NHibernate to execute them in one roundtrip at the moment when I actually want the results, using projects.ToList().

This will result in three SQL statements that are indeed executed in one roundtrip. The three statements will return the following results:
1) 1 row with your Project
2) x rows with the Partners and their Costs (and the Money), where x is the total number of Costs for the Project’s Partners
3) y rows with the Partners and their Addresses, where y is the total number of Addresses for the Project’s Partners

Your db should return 1+x+y rows, instead of x*y rows, which would be a cartesian product. I do hope that your DB actually supports that feature.

More Related Contents:

  1. How to Eager Load Associations without duplication in NHibernate?
  2. Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?
  3. What are the First and Second Level caches in (N)Hibernate?
  4. Inverse Attribute in NHibernate
  5. What’s the difference between session.Merge and session.SaveOrUpdate?
  6. How do you map an enum as an int value with fluent NHibernate?
  7. JSON.NET and nHibernate Lazy Loading of Collections
  8. List vs Set vs Bag in NHibernate
  9. When to use inverse=false on NHibernate / Hibernate OneToMany relationships?
  10. nHibernate mapping to custom types
  11. IndexOutOfRangeException Deep in the bowels of NHibernate
  12. Is this the right way to eager load child collections in NHibernate
  13. Unable to cast object of type NHibernate.Collection.Generic.PersistentGenericBag to List
  14. How to create OR statements for NHibernate?
  15. Binary Blob truncated to 8000 bytes – SQL Server 2008 / varbinary(max)
  16. How to save a child with assigned id in nhibernate
  17. NHibernate configuration for uni-directional one-to-many relation
  18. Generate XML mappings from fluent Nhibernate
  19. What is the difference between JoinQueryOver and JoinAlias?
  20. How do I view the SQL that is generated by nHibernate?
  21. Get Distinct result set from NHibernate using Criteria API?
  22. DateTime precision in NHibernate and support for DateTime2 in NHibernate SchemeExport
  23. Why doesn’t Entity Framework support ODBC?
  24. NHibernate: difference between Interceptor and Listener
  25. What exactly is “persistence ignorance”?
  26. How do add NOLOCK with nHibernate?
  27. Syntax to define a NHibernate Filter with Fluent Nhibernate?
  28. Cartesian product of x and y array points into single array of 2D points
  29. Is there a good LINQ way to do a cartesian product?
  30. How can you do paging with NHibernate?

Leave a Comment