Hi
This is not a question as such, I have been reserarching 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 beed 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 hierarhy 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 havn'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 querey 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 independantly to pull back the id's though.
Further details of using MultiCriteria to eager load can be found here..
Hope this of some help to someone