Using NHibernate to fetch Hierarchical information with SqlServer - Part - 3

In the previous post we saw how we can make use of Common Table Expression (CTE) and NHibernate to fetch Hierarchical information with SqlServer.  In this post we will see one more way in which we can map the CTE with NHibernate and this will be a more interesting way of mapping the CTE with NHibernate.

Just a small recap of the problem that we are trying to solve:

The Problem:

One Manager can have many subordinates (employees working under him) and one employee can have many managers

In the previous post we had defined a function that would take one argument, i.e. the ManagerId, this function would return a list of all the Sub-accounts associated with the manager at any level.  The function looked like
Then we mapped this function using a named query, but that's a little boring way of doing stuff.  Moreover, if we go that path then we wont get the advantages of seamlessly loading, lazy-loaded relationships by simply traversing the associations.  So lets see the another interesting way of mapping the function that we just declared.

Creating the View:

Let's create a view that would make use of this newly defined function to return the desired information.  We can then map the view using NHibernate to return all the sub-accounts of a given manager at any given level.

The script to create the view would look like
The view definition is pretty simple we just make use of a Cartesian product between the Employees table.  We have made use of our function to find the sub-accounts who are under a given manager.

This view returns employee_id, employee_name, manager_id and manager_name.  This is very similar to what the function returns but, the fun part here is that, we have managed to get rid of the mandatory manager_id parameter.  We can simply execute the view without any manager_id and we will get the entire manager sub-account hierarchy.

If we execute the following query we would get all the managers and their sub-accounts
The results of the above query would look like

All Employees and their sub-accounts

Now that the view is defined lets map it using NHibernate.  We are going to use the same EmployeeSubaccount that we had used in the last post, but map it against the view this time.
As you can see the mapping is a lot richer now.  We can refer the Employee object from the EmployeeSubaccount class and make use of the many-to-one mapping just very easily.

The calling code to the view has also changed a bit.
That's all we need. As you can see this technique is a lot more cooler than the previous technique of mapping the CTE with a named query.

That's all folks for mapping CTE with NHibernate!