Ayende @ Rahien

It's a girl

NHibernate tips & tricks: Efficiently selecting a tree

I run into the following in a code base that is currently being converted to use NHibernate from a hand rolled data access layer.

private void GetHierarchyRecursive(int empId, List<Employee> emps) {
    List<Employee> subEmps = GetEmployeesManagedBy(empId);
    foreach (Employee c in subEmps) {
        GetHierarchyRecursive(c.EmployeeID, c.ManagedEmployees);

GetHierarchyRecursive is a method that hits the database. In my book, a method that is calling the database in a loop is guilt of a bug until proven otherwise (and even then I’ll look at it funny).

When the code was ported to NHibernate, the question of how to implement this came up. And I wanted to avoid having the same pattern repeat itself. The fun part with NHibernate is that it make such things so easy.

		"select e from Employee e join fetch e.ManagedEmployees"
.SetResultTransformer(new DistinctRootEntityResultTransformer())

This will load the entire hierarchy in a single query. Moreover, it will build the organization tree correctly, so now you can traverse the entire graph without hitting empty spot or causing lazy loading.


08/28/2009 02:51 AM by

What is the significance of DistinctRootEntityResultTransformer? Why do I need that?

Dmitriy Nagirnyak
08/28/2009 04:08 AM by
Dmitriy Nagirnyak

The original code (with all its cons) retrieves the hierarchy for a given root only.

The HQL example you provided retrieves ALL the hierarchies with all the roots.

This seems to be totally different scenario comparing to the original code.


Also how would you efficiently retrieve the tree hierarchy starting from a specific root?


Ayende Rahien
08/28/2009 04:17 AM by
Ayende Rahien


DistinctRootEntityResultTransformer will filter the output in memory so the list of entities that you get will have no duplicates.

There are going to be duplicates in the result set if you don't do that because there is a join i the query

Ayende Rahien
08/28/2009 04:24 AM by
Ayende Rahien


You are correct, I haven't shown the calling code, which loaded all the employees by calling GetHierarchyRecursive.

The problem that you have here is that SQL has no good way of specifying hierarchies. Therefor, you would have to write DB specific tree code, and feed that to NH if you wanted to

load an entire tree in a single shot using N level parent.

A much easier task is to change the hierarchy so the relation between a parent and all its children (indirect and direct) is preserved in the DB.

That is a MUCH better proposition than doing hierarchical queries

Dmitriy Nagirnyak
08/28/2009 05:54 AM by
Dmitriy Nagirnyak


What exactly do you mean saying "relation between a parent and all its children (indirect and direct) is preserved in the DB."?

It would be interesting to see how you use NHibernate to effectively load the whole tree (maybe not in a single query:) ) as I saw it has been asked many times in the NH User Groups.

Additionally what is the difference between your HQL (with distinct root transformer) and simply this?

"from Employee"

they seem to return equals lists (maybe except of order).

08/28/2009 07:50 AM by

If only for this abstraction, i will use nhibernate ;)

this is excellent!

08/28/2009 12:24 PM by

The whole idea of delayed posts is.. refreshing. I was faced with the task to load a tree yesterday, and I had a choice: wait till thi post appears, or go to sleep.

Seriously, could you please post the mapping as well?

Another question is, suppose I want only descendants from a single entity. Can't I just set the children to eager loading and fetch this entity?

Thanks a lot for your posts about NH. I'm just learning it, and the official docs are a bit hard to grasp.

08/28/2009 02:32 PM by

Just another thought. Your posts on mappings are fantastic, but sometimes a noob like me needs a simple table showing which language constructs can be mapped with which mapping elements, so that I don't have to read all mapping chapter and your posts to figure out what I need in this particular case.

Sort of,

simple property => property

entity-valued property => one-to-many (or is it many-to-one?)

IDictionary => map

etc etc

Would be great if some of you NHibernate Masters could put such a table in a wiki.

Ayende Rahien
08/30/2009 06:57 AM by
Ayende Rahien


The difference is that mine will also pre-load all the ManagedEmployees.

As for the difference in the DB, it is simple

create Employee(Id, ManagedBy, ...)

create OrganizationHierarchy(EmpId, ManagedBy, Level)

Now you have a way to refer, using standard set notation, to the entire hierarchy.

Think about it as denormalizing that

Ayende Rahien
08/30/2009 06:58 AM by
Ayende Rahien


That presume that we can agree on the terms on the left side, though.

Nathan Palmer
08/31/2009 01:37 PM by
Nathan Palmer

That's great. Is there a corresponding way to do this using the Linq provider?


Ayende Rahien
09/01/2009 09:12 AM by
Ayende Rahien

Not with the 1.0 version, we will have those in the 2.0 one

09/01/2009 06:11 PM by

This snippet is very cool. Thanks a lot, I have to rewrite some code in my project :).

Is there any easy way to filter entire result's tree to contain only employees that have height (Employee table/entity contains Height column/property) more than 180cm ordering by height? E.g. only employees with height more than 180cm will be loaded from DB and the result tree will not contain any other employee. And employees in ManagedEmployees will be ordered by Height.

Neither "select e from Employee e join fetch e.ManagedEmployees where e.Height>180 order by e.Height" nor "select e from Employee e join fetch e.ManagedEmployees as empl where e.Height>180 and empl.Height > 180 order by e.Height, empl.Height" are 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.".

09/02/2009 01:06 AM by


This snippet doesn't work as well as I supposed. Yes, all tree will be loaded at once, but there is one "small" problem. During traversal through this tree (using several nesting cycles or recursion) NH will generate N additional queries, N equals to the count of leafs (employees who have no managed employees, parent without childs).

In order to make it working, I mean to remove these additional queries for leafs, the query should be changed to "select e from Employee e left join fetch e.ManagedEmployees". But using this query you'll get other errors - all leafs will be in the root of the result and each leaf will have reference to self (I added "public virtual Employee Manager {get; set;}")

Ayende Rahien
09/02/2009 11:01 AM by
Ayende Rahien


If you need to order the collection itself, you need an ordered collection. NHibernate doesn't allow to change sort order for collections on the fly without using filters.

For the leaf ones, you use left joins, yes.

I am not sure how you got the leafs to have a reference to themselves, please post the results to nh users

09/04/2009 03:46 AM by

Could you turn off lazy loading and then retrieve the first employee. Nhibernate will then try and populate the children property and hence retrieve the full tree.

Andrew Bullock
09/15/2009 01:16 PM by
Andrew Bullock

Should i expect to see NH issuing extra selects when I enumerate the child collection of the leaf nodes in the tree? I'm observing this happening and wondering if im doing something wrong :s

Ayende Rahien
09/15/2009 08:05 PM by
Ayende Rahien

No, you should not.

Post your question along with mapping & code to the nh users list

Comments have been closed on this topic.