Multiple Fetches in linq to nhibernate

Question

I was looking at this

Be careful not to eagerly fetch multiple collection properties at the same time. Although this statement will work fine:

var employees = session.Query() .Fetch(e => e.Subordinates) .Fetch(e => e.Orders).ToList();

I need to fetch 2 references so I would need to do something like that. Is there a better way of doing this.

I can't do .ThenFetchMany() as it goes to the into the child objects but the ones I am after on the same level.

Answer 1

Well, the query will still return the results you want, but as stated, it will return a cartesian product, i.e. the SQL query will return count(e.Subordinates) * count(e.Orders) results, which could add up pretty quickly, especially if you have more than just two collections.

NHibernate introduced Futures with the 2.1 release. Unfortunately there seems to be no way in the current NHibernate 3.0 release to make them work with NHibernate.Linq (session.Query<T>()).

Futures allow you to perform multiple queries in one roundtrip to the database (as long as the DB supports it, but most do). In that case you will only have count(e.Subordinates) + count(e.Orders) results, which is obviously the minimum.

Futures work with the criteria API, HQL and they are supposed to work with the new QueryOver API (I have not tested that, yet).

NHibernate.Linq does have Query().ToFuture() and Query().ToFutureValue(), but so far I only get Exceptions when I use them.

Edit:

I just checked again for the Linq API and it seems as if it is working if you do not use Fetch. The following will result in three SQL queries that are executed in one roundtrip. The total number of rows return will be 1 + count(Subordinates) + count(Orders).

int id = 1;

// get the Employee with the id defined above
var employee = repo.Session.Query<Employee>()
    .Where(o => o.Id == id)
    .ToFuture<Employee>();

// get the Subordinates (these are other employees?)
var subordinates = repo.Session.Query<Employee>()
    .Where(o => o.HeadEmployee.Id == id)
    .ToFuture<Employee>();

// get the Orders for the employee
var orders = repo.Session.Query<Order>()
    .Where(o => o.Employee.Id == id)
    .ToFuture<Order>();

// execute all three queries in one roundtrip
var list = employee.ToList();
// get the first (and only) Employee in the list, NHibernate will have populated the Subordinates and Orders
Employee empl = list.FirstOrDefault();

Thank you for having marked this as the answer anyway.