Eager Loading of Tree / Hierarchies using Nhibernate

Question

This is not a question as such, I have been researching this subject for the last few days and thought I would like to contribute something that pulled in a lot of the disparate ideas that I've been reading about and put forward my solution to the problem...

The problem being eager loading of n level child objects within Nhibernate and that nHibernate will not know the depth of the tree. I have seen this solved by using straight sql and Union All but unfortunately I couldn't get that to work, usually because nhibernate doesn't know that you've eager loaded the objects. So I looked at the following code for eager loading an object using criterion

        var children = Session.CreateCriteria<MenuNode>()
            .SetFetchMode("Children", FetchMode.Eager)
            .Add(Expression.Eq("Id", 1))
            .List<MenuNode>();

This will eager load the child for Id 1, from here you can use the In statement to eager load multiple entities at once. So I only need to work out all the node id's that belong to this hierarchy and eager load this using the In statement.

So if I create a parent table to the hierarchy and each node has a hierarchy id then I can get a list of all the node id's for this hierarchy using this hql

var sql = "select Distinct id from Nodes where (HierarchyId = :id) ";
var ids = Session.CreateSQLQuery(sql)
          .SetInt32("id", id)
          .List();

and from here eager load all children for this tree alone using

var children = Session.CreateCriteria<MenuNode>()
            .SetFetchMode("Children", FetchMode.Eager)
            .Add(Expression.In("Id", ids))
            .List<MenuNode>();

The only problem with this is that you haven't eager loaded the first level levels of nodes from the hierarchy parent table, which can be done normally..

var menu = Session.CreateCriteria<Menu>()
            .SetFetchMode("RootNodes", FetchMode.Eager)
            .Add(Expression.Eq("Id", id))
            .List<Menu>();

So that is it, in three SQL statements you have eager loaded a child object to n levels. To optimize even further I have used multi query to link the two main queries together and sending both statements at the same time. The statement to pull back the id's need to execute independently to pull back the id's though.

Further details of using MultiCriteria to eager load can be found here..

http://nhforge.org/blogs/nhibernate/archive/2008/09/06/eager-loading-aggregate-with-many-child-collections.aspx

Hope this of some help to someone

Adamok

Answer 1

I realize that you're not asking a question, but I thought I'd share the way I prefer to do this. See http://ayende.com/Blog/archive/2009/08/28/nhibernate-tips-amp-tricks-efficiently-selecting-a-tree.aspx

return session.CreateCriteria<MenuNode>()
    .SetFetchMode("Children", FetchMode.Join)
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .List<MenuNode>();

I don't know if this accomplishes everything that you intended to accomplish with your approach, but I've found it to be a very useful tool when dealing with hierarchical data.

Wychelm