Just a small recap of the problem that we are trying to solve:
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|
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!