In my previous post, we saw how we could take advantage of Common Table Expression (CTE) to fetch Hierarchical information with SqlServer.
In this post we are going to see How we can make NHibernate use CTE to fetch full Hierarchical information for a given employee.
The Problem:
Just so that we are all on the same page, lets start by trying to understand what is the problem we are trying to solve.
One Manager can have many subordinates (employees working under him) and one employee can have many managers
Because of this requirement we could build a complex multilevel hierarchy between managers and sub-accounts. We want an ability to view all the sub-accounts of a given manager. Sub-accounts would include direct sub-accounts or indirect sub-accounts. As seen in the previous post we were able to get all the sub-accounts of Batman using the following CTE.
If you look closely this CTE would return you all the subaccounts of Batman but what if we want all the subaccounts of some other manager? How do we integrate the CTE with NHibernate?
How do they do it?
Lets do it step by step.
Step One: lets remove the hard-coded manager_id from the CTE and get it into a variable. The updated query looks like
Notice how we extracted the manager_id into a variable and we can now easily change the manager_id and get the result for any manager. But still, the question stands, How are we going to use it from NHibernate?
Step Two: To do so we need a function that takes in the manager_id as its argument. We can then declare the CTE in the function and return the desired result for a given manager. The function body would look like
Notice how we added the root_manager_id to be returned from the function. This will help us in the next step. Other than that we didnt do any major change to the CTE.
Step Three: We are going to look at two ways of integrating the CTE with NHibernate. In this post we will look at a boring way of integrating the CTE with NHibernate. In the next post we will look at an more interesting way of integrating the CTE with NHibernate.
Since we now have a function already defined, all we need to do is write a named query which will make use of this function to return the required information.
Named Query out of our way, lets look at the EmployeeSubaccount class and its Fluent NHibernate Mapping.
Nothing very alarming in the entity class and its mapping.
Notice that that we are using employee_id and manager_id as the composite primary key. That's because one manager could have many sub-accounts and one sub-account could have many managers.
With the mapping in place lets look at the code to fetch all sub-accounts for any given manager.
We simply need a method that takes in the managerId and invokes the named query. This method returns the list of EmployeeSubaccount objects.
That's all folks! we have effectively made use of the CTE from NHiberante.
In the next post, we will look at how we can improve the mapping and get rid of the Named Query and still accomplish our task of fetching all sub-accounts of a given manager.
