NHibernate and loading tree of objects

[Zihotki]

Hi all,

Not so far ago Ayende pointed to very easy way to select all entities
of a tree at once in a single query -
http://ayende.com/Blog/archive/2009/08/28/nhibernate-tips-amp-tricks-efficiently-selecting-a-tree.aspx
. The solution is very interesting and useful. I tried to extend it to
load only some specific entities (filtered) and I filed.
My entity:
Employee
{
public int Id {get; set;}
public int Height {get; set;}
public IList<Employee> ManagedEmployees{get; protected set;}
public Employee Manager {get; set;}
}
where ManagedEmployees is one-to-many.

I tried to load a tree with employees that have height more than 180cm
(e.g. only employees with height more than 180cm will be selected and
the result tree will be constructed using only filtered employees).
Query "select e from Employee e join fetch e.ManagedEmployees where
e.Height>180" filters only top level employees and all childs are not
filtered.
Query "select e from Employee e join fetch e.ManagedEmployees as empl
where e.Height>180 and empl.Height > 180" is not working because "A
fetch join does not usually need to assign an alias, because the
associated objects should not be used in the where clause (or any
other clause). Also, the associated objects are not returned directly
in the query results. Instead, they may be accessed via the parent
object." ( http://nhforge.org/doc/nh/en/index.html#queryhql-joins ).
There are no way to use a nested select in from clause (e.g. "select e
from (select e2 from Employee e2 where e2.Height > 180) e");

And I wonder, is there any way to do such kung-fu? Can it be done
using Criteria api? At the moment I'm trying to play with filters and
I'll update this thread with results. Any help will be appreciated.

Regards,
zihotki

[zihotki]

I found a problem with Ayende's solution. It doesn't working well as I
supposed. Yes, tree of employees will be loaded at once. But during
traversal through the tree you'll get N additional queries - one for
each leaf (an employee without managed employees).

Entity:
public class Employee : Entity
{
public Employee()
{
ManagedEmployees = new List<Employee>();
}

public virtual int Height { get; set; }
public virtual IList<Employee> ManagedEmployees { get; set; }
public virtual Employee Manager { get; set; }
}

My full test mapping:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-
access="property" auto-import="true" default-cascade="none" default-
lazy="true">
<class xmlns="urn:nhibernate-mapping-2.2" name="Test.Employee,
Test" table="Employees">
<id name="Id" type="System.Int32, mscorlib" unsaved-value="0">
<column name="Id" />
<generator class="hilo">
<param name="max_lo">100</param>
</generator>
</id>
<property name="Height" type="System.Int32, mscorlib">
<column name="Height" />
</property>
<bag name="ManagedEmployees">
<key>
<column name="EmployeeFk" />
</key>
<one-to-many class="Test.Employee, Test"/>
<filter name="BagHeightFilter" condition=":MaxHeight = Height"/>
</bag>
<one-to-one class="Test.Employee, Test" name="Manager"/>
<filter name="HeightFilter" condition=":MaxHeight = Height"/>
</class>
<filter-def name="HeightFilter">
<filter-param name="MaxHeight" type="int"/>
</filter-def>
<filter-def name="BagHeightFilter">
<filter-param name="MaxHeight" type="int"/>
</filter-def>
</hibernate-mapping>

For tests I use the following tree of objects and all entities are
evicted from the session after insert of data:
empl id101, h120
empl id105, h100
empl id104, h120
empl id103, h120
empl id107, h100
empl id102, h120
empl id106, h100
empl id108, h120

Code used to insert test data:

var session = NHibernateSession.Current;

var employees = new Employee[]
{
new Employee {Height = 120}, //0, 101
new Employee {Height = 120}, //1, 102
new Employee {Height = 120}, //2, 103
new Employee {Height = 120}, //3, 104
new Employee {Height = 100}, //4, 105
new Employee {Height = 100}, //5, 106
new Employee {Height = 100}, //6, 107
new Employee {Height = 120}, //7, 108
};

employees[0].ManagedEmployees.Add(employees[2]);
employees[2].Manager = employees[0];
employees[0].ManagedEmployees.Add(employees[3]);
employees[3].Manager = employees[0];
employees[0].ManagedEmployees.Add(employees[4]);
employees[4].Manager = employees[0];
employees[1].ManagedEmployees.Add(employees[5]);
employees[5].Manager = employees[1];
employees[2].ManagedEmployees.Add(employees[6]);
employees[6].Manager = employees[2];

foreach (var employee in employees)
{
session.SaveOrUpdate(employee);
}

session.Flush();

foreach (var employee in employees)
{
session.Evict(employee);
}

Code to select data and to traverse through the tree:

var query = session.CreateQuery("select e from Employee e join fetch
e.ManagedEmployees");
query.SetResultTransformer(CriteriaSpecification.DistinctRootEntity);

var result = query.List<Employee>();

foreach (var employee in result)
{
foreach (var employee1 in employee.ManagedEmployees)
{
foreach (var employee2 in employee1.ManagedEmployees)
{
Assert.That(1, Is.Not.EqualTo(employee2.Height));
}
}
}

During traversal through this tree we'll get 4 additional queries:
SELECT managedemp0_.EmployeeFk as EmployeeFk2_, managedemp0_.Id as
Id2_, managedemp0_.Id as Id1_1_, managedemp0_.Height as Height1_1_,
employee1_.Id as Id1_0_, employee1_.Height as Height1_0_ FROM
Employees managedemp0_ left outer join Employees employee1_ on
managedemp0_.Id=employee1_.Id WHERE managedemp0_.EmployeeFk=@p0;@p0 =
104
SELECT managedemp0_.EmployeeFk as EmployeeFk2_, managedemp0_.Id as
Id2_, managedemp0_.Id as Id1_1_, managedemp0_.Height as Height1_1_,
employee1_.Id as Id1_0_, employee1_.Height as Height1_0_ FROM
Employees managedemp0_ left outer join Employees employee1_ on
managedemp0_.Id=employee1_.Id WHERE managedemp0_.EmployeeFk=@p0;@p0 =
105
SELECT managedemp0_.EmployeeFk as EmployeeFk2_, managedemp0_.Id as
Id2_, managedemp0_.Id as Id1_1_, managedemp0_.Height as Height1_1_,
employee1_.Id as Id1_0_, employee1_.Height as Height1_0_ FROM
Employees managedemp0_ left outer join Employees employee1_ on
managedemp0_.Id=employee1_.Id WHERE managedemp0_.EmployeeFk=@p0;@p0 =
106
SELECT managedemp0_.EmployeeFk as EmployeeFk2_, managedemp0_.Id as
Id2_, managedemp0_.Id as Id1_1_, managedemp0_.Height as Height1_1_,
employee1_.Id as Id1_0_, employee1_.Height as Height1_0_ FROM
Employees managedemp0_ left outer join Employees employee1_ on
managedemp0_.Id=employee1_.Id WHERE managedemp0_.EmployeeFk=@p0;@p0 =
107

Main query:
select employee0_.Id as Id1_0_, managedemp1_.Id as Id1_1_,
employee0_.Height as Height1_0_, managedemp1_.Height as Height1_1_,
managedemp1_.EmployeeFk as EmployeeFk0__, managedemp1_.Id as Id0__
from Employees employee0_ inner join Employees managedemp1_ on
employee0_.Id=managedemp1_.EmployeeFk

and it returns
Id1_0_ Id1_1_ Height1_0_ Height1_1_ EmployeeFk0__ Id0__
101 103 120 120 101 103
101 104 120 120 101 104
101 105 120 100 101 105
102 106 120 100 102 106
103 107 120 100 103 107

Now interesting part:
Empl102.Id is 102
Empl102.ManagedEmployees[0].Manager.Id is 106 but should be 102
Empl102.ManagedEmployees[0].Id is 106

Empl108.Manager.Id is 108 but should be null
Empl108.Id is 108

And the same is with other objects.

When filters are enabled:
session.EnableFilter("HeightFilter").SetParameter("MaxHeight", 120);
session.EnableFilter("BagHeightFilter").SetParameter("MaxHeight",
120);

the result is incorrect too, the query is
select employee0_.Id as Id1_0_, managedemp1_.Id as Id1_1_,
employee0_.Height as Height1_0_, managedemp1_.Height as Height1_1_,
managedemp1_.EmployeeFk as EmployeeFk0__, managedemp1_.Id as Id0__
from Employees employee0_ inner join Employees managedemp1_ on
employee0_.Id=managedemp1_.EmployeeFk and @p0 = managedemp1_.Height
where @p1 = employee0_.Height;@p0 = 120, @p1 = 120

and it returns only 2 rows
Id1_0_ Id1_1_ Height1_0_ Height1_1_ EmployeeFk0__ Id0__
101 103 120 120 101 103
101 104 120 120 101 104

and this rowset doesn't contains all data to create the tree

The solution for filters is to use left join:
session.EnableFilter("HeightFilter").SetParameter("MaxHeight", 120);
session.EnableFilter("BagHeightFilter").SetParameter("MaxHeight",
120);
var query = session.CreateQuery("select e from Employee e left join
fetch e.ManagedEmployees");

Result query is:
select employee0_.Id as Id1_0_, managedemp1_.Id as Id1_1_,
employee0_.Height as Height1_0_, managedemp1_.Height as Height1_1_,
managedemp1_.EmployeeFk as EmployeeFk0__, managedemp1_.Id as Id0__
from Employees employee0_ left outer join Employees managedemp1_ on
employee0_.Id=managedemp1_.EmployeeFk and @p0 = managedemp1_.Height
where @p1 = employee0_.Height;@p0 = 120, @p1 = 120
And result rowset:
Id1_0_ Id1_1_ Height1_0_ Height1_1_ EmployeeFk0__ Id0__
101 103 120 120 101 103
101 104 120 120 101 104
102 NULL 120 NULL NULL NULL
103 NULL 120 NULL NULL NULL
104 NULL 120 NULL NULL NULL
108 NULL 120 NULL NULL NULL

This rowset contains all necessary filtered data to create the tree.
And the tree is created. But there are some stranges too :)
count of elements of 1st level is 5 (ids: 101, 102, 103, 104, 108)
instead of 3 (ids: 101, 102, 108)
Empl101.Id is 101
Empl101.Manager.Id is 101 but should be null

Empl101.ManagedEmployees[0].Id is 103
Empl101.ManagedEmployees[0].Manager.Id is 103 but should be 101


Resume, at this moment I don't know how to load all tree using single
query. Ayende's solution is not working properly for my mapping and
there are problems with applying filters. I didn't test it with any
other mapping. Probably it can work, but it seems to me that we need
in some additional Voodoo magic.

So the question is still opened - is there any way to load entire tree
correctly applying data filters in single of few queries?

PS. sorry for long mail :)

Regards,
zihotki