Using Common Table Expressions to retrieve hierarchical data in NHibernate

Suppose that we have an hierarchical structure of employees in an organization. The data is stored in the MyEmployees table.

CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL 
);

We have the following NHibernate mapping file for the Employee class.

<class name="Employee" table="MyEmployees" >
    <id name="Id" column="EmployeeID">
      <generator class="native" />
    </id>
    <property name="FirstName" />
    <property name="LastName" />
    <property name="Title" />
    <property name="DeptID" />
    <many-to-one name="Manager" class="Employee" column="ManagerID" />
</class>

To map direct reports of an employee, we can easily do it via one-to-many mapping.

<set name="DirectReports">
    <key column="ManagerID"/>
    <one-to-many class="Employee"/>
</set>

But how do we map all reports of a given employee?

<set name="AllReports">
    ???????
</set>

As we see, mapping direct children is supported in NHibernate. But up till now (version 3.1) there has been no support for hierarchical queries. To get all descendents, we would have to do a recursive N+1 query.

Fortunately NHibernate has excellent support for native SQL. We can use Common Table Expressions (CTE) to solve this very elegantly. CTEs are not common across all Databases. But if you are using SQL Server 2005 and beyond, CTEs provides a very powerful way to do recursive queries. It is capable of handling hierarchical queries much faster than any code in C#.

Here's the SQL query using CTE to retrieve all descendents:

WITH EmployeeHeirarchy (ManagerID, EmployeeID)
    AS
    (
        SELECT ManagerID, EmployeeID
        FROM   MyEmployees
        WHERE  ManagerID = :id

        UNION ALL

        SELECT me.ManagerID, me.EmployeeID
        FROM   MyEmployees me
        JOIN   EmployeeHeirarchy eh ON eh.EmployeeID = me.ManagerID
    )

SELECT :id AS RootMgrID, e.*
FROM   EmployeeHeirarchy eh
JOIN   MyEmployees e ON eh.EmployeeID = e.EmployeeID

The parameter :id is the Id of the root manager for whom we are retrieving all descendent employees. The RootMgrID column is needed as part of the return SELECT statement. It will be used by NHibernate as the foreign key to map Employee to AllReports collection, as you will see below.

We will put the above query in a Named SQL Query in the mapping file. That way it is not hardcoded in the code and it lives along will other mapping definitions.

<sql-query name="descendents" >
  <load-collection alias="e" role="Employee.AllReports" />
  
  ...sql query here...

</sql-query>

Now we reference this named query in the class mapping for the AllReports collection loading.

<set name="AllReports">
    <key column="RootMgrID" />
    <one-to-many class="Employee" />
    <loader query-ref="descendents"/>
</set>

That's all that there is to it. We now have a nice property called AllReports in the Employee class, which is extremely fast in crawling through the whole hierarchy tree and retrieve all employees.

Here's the complete Employee mapping file:

<class name="Employee" table="MyEmployees" >
    <id name="Id" column="EmployeeID">
      <generator class="native" />
    </id>
    <property name="FirstName" />
    <property name="LastName" />
    <property name="Title" />
    <property name="DeptID" />
    <many-to-one name="Manager" class="Employee" column="ManagerID" />

    <set name="DirectReports">
        <key column="ManagerID"/>
        <one-to-many class="Employee"/>
    </set>

    <set name="AllReports">
        <key column="RootMgrID" />
        <one-to-many class="Employee" />
        <loader query-ref="descendents"/>
    </set>
</class>

<sql-query name="descendents" >
  <load-collection alias="e" role="Employee.AllReports" />
    WITH EmployeeHeirarchy (ManagerID, EmployeeID)
    AS
    (
        SELECT ManagerID, EmployeeID
        FROM   MyEmployees
        WHERE  ManagerID = :id

        UNION ALL

        SELECT me.ManagerID, me.EmployeeID
        FROM   MyEmployees me
        JOIN   EmployeeHeirarchy eh ON eh.EmployeeID = me.ManagerID
    )

    SELECT :id AS RootMgrID, e.*
    FROM   EmployeeHeirarchy eh
    JOIN   MyEmployees e ON eh.EmployeeID = e.EmployeeID
</sql-query>