Nhibernate is a great Object Relational Mapping library. It allows you to forget about your database and focus on writing classes that interact with each other without any tedious CRUD code. It works beautifully, up until the point where you need to optimize your code.

Loading data with NHibernate can take a long time when you need to join results from multiple tables for a master-detail view. You can read all the records from the master table in one go, but when you access the fields of the resulting object instances to get at the child elements, then each access will result in a separate database query. Worse – if your mappings are not configured to use lazy loading, NHibernate will execute these queries even before you access the child fields, so let’s assume that you’ve configured NHibernate to use lazy loading, at least. If you’ve got 100 master objects, with 10 detail objects each, then you’re looking at one query to load all the master objects, and 100*10 = 1000 queries to load all the detail objects!

Loading a data tree

An example of this is a tree of data in your application. Suppose you want to show a tree like so:

Here, we’re showing a list of provinces of Mozambique, with districts as child elements, and so-called administrative posts (sub districts) as level-2 child elements. This is in fact a join of three tables: a province table, a district table, and an administrative post table. At the code level, we have the following classes:

Province, District and AdministrativePost are all derived from LocationBase, since they all have a name, a latitude and a longitude (so they can be shown on a map) as well as an ID (not shown in diagram). All three entities have their own database table.

Thousands of queries

The simple way to the data from the database into a TreeView in our application, is to load all provinces first:

var res = from item in session.Query()
          select item;
IList provinces = res.ToList();

NHibernate will grab all these provinces with a single query. We can then loop through the list of provinces and create a TreeNode for each province:

foreach(Province p in provinces)
{
  TreeNode node = new TreeNode(p.Name);
  treeView.Nodes.Add(node);
}

In order to get the districts in, we’ll change this code to:

foreach(Province p in provinces)
{
  TreeNode node = new TreeNode(p.Name);
  treeView.Nodes.Add(node);
  foreach(District d in p.Districts)
  {
    TreeNode dNode = new TreeNode(d.Name);
    node.Nodes.Add(dNode);
  }
}

This is where things go awry. Whenever we access the Province property p.Districts, NHibernate will immediately execute a query to retrieves the list of districts for the province p from the database. Therefore, for n provinces, the total number of queries executed is 1 (for the list provinces) + n (one for each province) = 1 + n.

If we add in administrative posts, the situation gets worse. Suppose we have n provinces and on average m districts per province. The number of queries will when be 1 + n * m. In real numbers, for 11 provinces and 20 districts per province, we would have 1 + 11 * 20 = 221 queries!

If we had more than 3 levels, the number of queries would really go through the roof. Also, if the Name fields of the various location classes were lazy, then we would have to add an additional query for each access of that field – so it definitely must not be lazily loaded.

Using a single query (without NHibernate)

Wouldn’t it be great if all this data could be loaded in a single query? In fact, that’s probably what you would do if you weren’t using NHibernate. You’d write a query that joined the province, district and administrative_post tables together:

SELECT
  province.Id AS pId, province.Name AS pName,
  district.Id AS dId, district.Name AS dName,
  administrative_post.Id AS aId, administrative_post.Name AS aName
FROM province
LEFT JOIN district ON district.Province=province.Id
LEFT JOIN administrative_post ON administrative_post.District=district.Id
ORDER BY province.Name, district.Name, administrative_post.Name

This would get you a list like this:

1, province1, 1, district1, 1, post1
1. province1, 1, district1, 2, post2
1. province1, 2. district2, 3, post3
1. province1, 2. district2, 4, post4
1. province1, 3. district3, NULL, NULL
1. province1, 4. district4, 5, post5
2. province2, 5, district5, 6, post6
etc.

In this dataset, the districts are repeated if they contain more than one administrative post, and provinces are repeated if they contain more than one district – but a least everything was retrieved using a single query. The trick is now to traverse the result set, and create Province, District and AdministrativePost instances as necessary. A bit of “are we still in the same province” logic will do the trick.

Using a single query with NHibernate

Doing it all with a single query without NHibernate is all well and good, but we actually are using NHibernate and we should not circumvent it to access the database ourselves. We need to convince NHibernate itself to do it all with a single query. And it turns out that we can.

The solution is to use NHibernate’s HQL (Hibernate Query Language):

IQuery query = session.CreateQuery(
    "SELECT province.Id, province.Name, "
  + "  district.Id, district.Name, "
  + "  post.Id, post.Name "
  + "FROM Province province "
  + "LEFT JOIN province.Districts district "
  + "LEFT JOIN district.AdministrativePosts post "
  + "ORDER BY province.Name, district.Name, post.Name "
);
<IList>Object[] lst = query.List<Object[]>();

HQL is much like ordinary SQL, but instead of using table column names, you supply class member names in your SELECT clause. Also, your joins do not refer IDs, but rather the Districts and AdministrativePosts collection members. Behind the scenes, NHibernate will convert these to ID comparisons. Ordering the result set is an afterthought that will make putting data in your tree more straightforward.

When this query is executed, it does not return instances of Province, District or AdministrativePost like NHibernate usually would. Instead, it turns a list of Object instances, each with six fields which correspond to the fields in the SELECT clause. In order to convert this list into project object relationships, we can do:

IList provinces = new List();
Province province = null;
District district = null;
foreach (Object[] obj in lst)
{
  if (province == null || (uint)obj[0] != province.Id)
  {
    province = new Province((string)obj[1], 0, 0);
    province.Id = (uint)obj[0];
    provinces.Add(province);
  }
  if (obj[2] != null && (district == null || (uint)obj[2] != district.Id))
  {
    district = new District((string)obj[3], 0, 0);
    district.Id = (uint)obj[2];
    district.Province = province;
    province.Districts.Add(district);
  }
  if (obj[4] != null && (post == null || (uint)obj[4] != post.Id))
  {
    AdministrativePost post = new AdministrativePost((string)obj[5], 0, 0);
    post.Id = (uint)obj[4];
    post.District = district;
    district.AdministrativePosts.Add(post);
  }
}

This will yield a list of provinces, with district children, which in turn have AdministrativePost children – ready to turn into a tree!