Force an eager select in NHibernate

Question

I am trying to eagerly fetch collections using selects, but all I am getting is inner joins. What is going on?

Session.CreateCriteria(typeof(Foo))
    .SetFetchMode("Bars", FetchMode.Select)
    .CreateAlias("Bars", "b")
    .SetFetchMode("b.Bazes", FetchMode.Select)
    .List();

I have tried changing FetchMode to Eager but that doesn't work - I still get inner joins instead of seperate selects. I'm not sure where it is even getting the inner join from, because nothing in the docs talks about FetchMode causing inner joins. Is it possible to get eager selects?

Update OK I worked out that creating an alias causes an inner join. So I can use .CreateAlias("Bars", "b", JoinType.None), but then the fetching of b.Bazes reverts to lazy loading. Urgh.

Slipover

Answer 1

An INNER JOIN is how NHibernate will load your records and their related child records. This is generally the most efficient way of doing it.

If it was to use multiple SELECT statements then the query for children would need to somehow include the criteria for the parent. An INNER JOIN makes it easy to get just related children, NHibernate will correctly split this out into multiple entities after running the query.

I believe Entity Framework 4 will let you do multiple queries and 'magically' re-attach related objects, but I'm not aware of NHibernate having such a feature (I'm sure someone will correct me if I'm wrong on this).

Divvy

But the default join type is usually left outer join isn't it? Inner joins don't work if the foreign key is nullable, or as in my case, where Bars could be empty. Also the docs seem to indicate that you can do an eager select. – Slipover

Answer 2

For nhibernate to eager load entities left outer join is used. So you need to change your code like this:

Session.CreateCriteria(typeof(Foo))
.SetFetchMode("Bars", FetchMode.Select)
.CreateAlias("Bars", "b", JoinType.LeftOuterJoin)
.SetFetchMode("b.Bazes", FetchMode.Select)
.List();

It helped me in similar scenario.

Commensurable

Answer 3

Session.CreateCriteria(typeof(Foo))
   .SetFetchMode("Bars", FetchMode.Select)
   .CreateAlias("Bars", "b")  <-- this line triggers a join ( think )
   .SetFetchMode("b.Bazes", FetchMode.Select) 
   .List();

If you really do not want joins you can specify fetch="select" in the mapping, but that would cause a N+1 select which is not recommended (one select for each Foo entity, and then one for each Baze)

Incombustible