Eagerly load recursive relation

Question

I have a recursive one-to-many relationship that has the default lazy value of true. What code can I write against the NH API that will efficiently retrieve the ENTIRE tree AS IF I had lazy="false" on the SubCategories mapping?

Here's the recursive one-to-many relationship:

<class name="Category" lazy="false">
    ...
    <list name="SubCategories" fetch="subselect">
            <key column="ParentCategoryID"/>
            <index column="PositionInList"/>
            <one-to-many class="Category"/>
    </list>

I don't specify lazy="false" on the list since laziness is required in about half the queries I need to run. I have fetch="subselect" on the list as an optimization for when I do manage to retrieve the entire tree.

I've tried the ICriteria API:

session.CreateCriteria<Category>().SetFetchMode( "SubCategories", FetchMode.Eager ).Add( Restrictions.IsNull("ParentCategory") ).SetResultTransformer( CriteriaSpecification.DistinctRootEntity ).List<Category>();

but that only eagerly loaded only the first level in the hierarchy.

Answer 1

See Ayende's site: Efficiently Selecting a Tree. I have successfully used this technique in my own applications. With ICriteria, it looks like this:

session.CreateCriteria<Category>()
    .SetFetchMode("SubCategories", FetchMode.Join)
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .List<Category>()
    .Where(x => x.ParentCategory == null);

The main difference between this version and what you tried is how the "ParentCategory == null" filter is applied. It has to be left out of the query that is sent to the database in order to retrieve the whole tree - but we still need the query to only return the root nodes of the tree, so we'll use linq to find those after the database query has completed.

Answer 2

I used Daniel's code as a bases for solving the problem. I also experimented with the equivalent HQL that I shared below. The HQL executed slightly faster, but I went with ICriteria since I could then choose between FetchModel.Join and FetchModel.Lazy.

session.CreateQuery( "from Category as c left join fetch c.SubCategories" )
  .SetResultTransformer( new DistinctRootEntityResultTransformer() )
  .List<Category>()
  .Where( c => c.ParentCategory == null );