Sunday, April 29, 2012

How To Force NHibernate to use discriminator while fetching entities for Single Table Per Class Hierarchy Mapping

Recently we faced a very interesting issue with the Single Table Per Class Hierarchy mapping in NHibernate.

The Issue:

NHibernate would not use the discriminator values while fetching the subclasses of entities mapped using the Single Table Per Class Hierarchy mapping.

What?

To better understand the issue lets consider a simple mapping of three entities.  We want to map Employee, Manager and Department entities.  Things to notice about these entities are:
  • Manager is also an Employee
  • Employee and Manager form an Inheritance Hierarchy
  • One Department can have many managers 
  • One Department can have many employees 
Manager and Employee form an Inheritance Hierarchy, for the sake of simplicity we are not going to add any other properties to the Manager class.  We will map Employee and Manager entities using Single Table Per Class Hierarchy mapping stretegy.

Lets look at the C# classes for Employee, Manager and Department
Nothing special about these classes. Lets look at their mapping files. As you can see Employee and Manager have been mapped using the Single Table Per Class Hierarchy Mapping strategy and the Department mapping has many employees and managers. With me so far?

Alright then, Lets write a Test case to load the department and its associated managers.

Data Setup:

Lets execute the following insert statements
We have setup one department with name Operations and two employees SpiderMan (who is a normal employee) and BatMan (who is a manager) i.e. we have *only one manager* associated with the department Operations.

All set!

Test code:
If you run the test now it would fail on the line that asserts that the Managers count associated with the department Operations = 1.

Why?

Lets look at the simplified version of the queries that get fired when the test is run
The first query is to fetch the department with ID=1, no surprises there! But lets focus on the second query.

The second query tries to load all the managers associated with the department id=1.

Notice that we are only interested in the managers and not the employees.  Employees and Managers are both stored in the employees tables.  We had two records in the employees table that have department id=1 (SpiderMan - The Employee and BatMan - The Manager).  But out of those two records we were only interested in the BatMan - The Manager record.

If we now look at the second query again
Notice that the discriminator value was not added to the query.

By default NHibernate will not add the discriminator criteria to the query when loading the Managers collection.  Because of this the department.Managers will also have the SpiderMan - The Employee record.  Hence, our test case fails.

How to Fix this:

The solution is pretty simple.  We need to inform NHibernate to always add the discriminator criteria when selecting the records from the employees table.  For this we need to update the EmployeeMap as follows
The only change is that after the DiscriminateSubClassesOnColumn call we are invoking the AlwaysSelectWithValue method. This tells NHibernate to always add the discriminator criteria when selecting the records from the employees table. 

If you now run the test you will notice that it passes. Lets look at the queries fired
You will notice that the second query has the condition managers0_.emp_type = 'Manager' added to it. This makes sure that we only get managers associated with the department id=1.

That's all folks!
Have some Fun!