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

If you are using Linq on your NHibernate, you can simplify cartesian-prevention with this:

int projectId = 1;
var p1 = sess.Query<Project>().Where(x => x.ProjectId == projectId);


p1.FetchMany(x => x.Partners).ToFuture();

sess.Query<Partner>()
.Where(x => x.Project.ProjectId == projectId)
.FetchMany(x => x.Costs)
    .ThenFetch(x => x.Total)
.ToFuture();

sess.Query<Partner>()
.Where(x => x.Project.ProjectId == projectId)
.FetchMany(x => x.Addresses)
.ToFuture();


Project p = p1.ToFuture().Single();

Detailed explanation here: http://www.ienablemuch.com/2012/08/solving-nhibernate-thenfetchmany.html

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.