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