I'm bad at math but I kind get idea what cartesian product is.
Here is my situation (simplified):
public class Project{
public IList<Partner> Partners{get;set;}
}
public class Partner{
public IList<PartnerCosts> Costs{get;set;}
public IList<Address> Addresses{get;set;}
}
public class PartnerCosts{
public Money Total{get;set;}
}
public class Money{
public decimal Amount{get;set;}
public int CurrencyCode{get;set;}
}
public class Address{
public string Street{get;set;}
}
My aim is to effectively load entire Project.
Problem of course is:
- If I try to eager load partners and their costs, query returns gazillion rows
- If I lazy load Partner.Costs, db gets request spammed (which is a bit faster than first approach)
As I read, common workaround is to use MultiQueries, but I kind a just don't get it.
So I'm hoping to learn through this exact example.
How to effectively load whole Project?
P.s. I'm using NHibernate 3.0.0.
Please, do not post answers with hql or string fashioned criteria api approaches.
Best Answer
Ok, I wrote an example for myself reflecting your structure and this should work:
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, thePartners
with theirCosts
and thePartners
with theirAddresses
. By using the.Futures()
I basically tell NHibernate to execute them in one roundtrip at the moment when I actually want the results, usingprojects.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.