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) {
        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.