+972 52-548-6969
NHibernate tips & tricks: Efficiently selecting a tree
time to read 2 min | 224 words
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) { emps.Add(c); 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.
session.CreateQuery( "select e from Employee e join fetch e.ManagedEmployees" )
.SetResultTransformer(new DistinctRootEntityResultTransformer())
.List<Employee>();
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.
Comments
What is the significance of DistinctRootEntityResultTransformer? Why do I need that?
This may answer your question:
nhforge.org/.../...esults-from-joined-queries.aspx
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.
Right?
Also how would you efficiently retrieve the tree hierarchy starting from a specific root?
Cheers.
Rob,
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
Dmitriy,
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
Oren,
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).
If only for this abstraction, i will use nhibernate ;)
this is excellent!
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.
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.
Dmitriy,
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
ulu,
That presume that we can agree on the terms on the left side, though.
That's great. Is there a corresponding way to do this using the Linq provider?
Nathan
Not with the 1.0 version, we will have those in the 2.0 one
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.".
Ayende,
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;}")
Zihotki,
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
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.
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
No, you should not.
Post your question along with mapping & code to the nh users list
Comment preview