NHibernate - define fetching strategy dynamically

Question

Let me explain the problem - hopefully I have defined it well in the title but I want to be sure.

I have a linq query that pulls back a bunch of objects (say Foos). Each Foo holds a reference to a User. Each User holds a reference to a Person:

public class Foo
{
  //properties omitted...
  public User CreatedBy {get;}
}
public class User
{
  //properties omitted...
  public Person Person {get;set;}
}

As the object structure would suggest, in the database, Foo relates many-to-one to User, and User relates many-to-one to Person.

When I run the query, I get a single SELECT for the Foos, then a SELECT each for all the Users and People. Clearly I would much prefer a single SELECT with a couple of joins.

I don't necessarily want to specify in my mapping config that Foos ALWAYS eager fetch the User, or that Users ALWAYS eager fetch the Person, but I would like to be able to specify that in this instance.

Is there a way to do that?

Thanks

David

Answer 1

Both Udi Dahan and Ritesh Rao offer example implementations of dynamic fetching strategies for NHibernate, this should give you a good starting point.

Answer 2

All the NHibernate query methods have ways of specifying eager fetching.

For Criteria, you have SetFetchMode.

For HQL, you have [inner|left] join fetch.

For Linq yo have Expand (2.x contrib) / Fetch (3.x).

For SQL you have AddJoin.

Answer 3

Additionally to Diegos nice answer: You can also use batching. This reduces the N+1 problem without much pain:

use batch-size on class level:

<class name="Person" batch-size="20">
...
</class>

use batch-size on collection level:

<map
    name="SomeCollection"
    batch-size="20">
  ...
</map>

When ever one of these references is loaded, NHibernate loads 20 at once using a query like this:

select ... from Person where user_fk in (23, 34, 6, 667, 6745, 234 ....)

So it turns N+1 to N / 20 + 1, which is pretty good.