12.23.2010

Using an NHibernate discriminator with a stored procedure

Recently I needed to split the results of a stored procedure into one of two classes based on one of the columns using NHibernate. Having recently discovered discriminators, I applied the discriminator tag to my mapping file and created my subclasses. (I've changed the names of the classes and columns, so please excuse the lame example.) I want to generate either a teacher or a student object for a specified semester from each row in the stored procedures result set. Teacher if the class_room value is not null, student otherwise. Initially I tried to accomplish this using a discriminator formula.

<hibernate-mapping  xmlns="urn:nhibernate-mapping-2.2" assembly="Core" namespace="Core">
  <class name="AbstractPerson" abstract="true">
    <id name="Id" />
    <property name="FirstName" />
    <property name="LastName" />
 <discriminator formula="case when class_room is null then 1 else 0 end" />
    <subclass discriminator-value="0" name="Student">
      <property name="DormRoom" />
    </subclass>
    <subclass discriminator-value="1" name="Teacher">
      <property name="Classroom" />
    </subclass>
  </class>
  <sql-query name="GetPeopleWithLocation" callable="true">
    <return class="AbstractPerson">
      <return-property name="FirstName" column="first_name" />
      <return-property name="LastName" column="last_name" />
      <return-property name="DormRoom" column="dorm_room" />
   <return-property name="ClassRoom" column="class_room" />
    </return>
    exec get_person_location :semester_id
  </sql-query>
</hibernate-mapping>

Of course, I had put the discriminator in the incorrect location and got the following error:

XML validation error: The element 'class' in namespace 'urn:nhibernate-mapping-2.2' has invalid child element 'discriminator' in namespace 'urn:nhibernate-mapping-2.2'
Doing some research, I realized that the discriminator tag needs to be directly under the id element. I moved it and executed my test again (you are doing TDD right?).

This time the query ran, but I got a rather cryptic error:

[SQL: exec get_person_location :semester_id] ---> System.IndexOutOfRangeException: clazz_0_
Based on previous experience with discriminators and some research, I believe this error was caused because NHibernate is attempting to add the discriminator formula to the sql statement that is being executed. This is obviously problematic when using a stored procedure. Back to the drawing board.

I continued researching and finally found the return-discriminator tag from the Hibernate documentation. This tag can be used in the return class to specify which column should be used as the discriminator. Now that sounds promising! Another visit to the mapping file yeilded another error:

XML validation error: The element 'return' in namespace 'urn:nhibernate-mapping-2.2' has invalid child element 'return-discriminator' in namespace 'urn:nhibernate-mapping-2.2'.
I had placed the return-discriminator tag at the end of the return class. It must be the first element after the return tag.

The final mapping file looks like this:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Core" namespace="Core">
  <class name="AbstractPerson" abstract="true">
    <id name="Id" />
 <<discriminator column="class_room" />
    <property name="FirstName" />
    <property name="LastName" />
    <subclass discriminator-value="null" name="Student">
      <property name="DormRoom" />
    </subclass>
    <subclass discriminator-value="not null" name="Teacher">
      <property name="ClassRoom" />
    </subclass>
  </class>
  <sql-query name="GetPeopleWithLocation" callable="true">
    <return class="AbstractPerson">
      <return-discriminator column="class_room" />
      <return-property name="FirstName" column="first_name" />
      <return-property name="LastName" column="last_name" />
      <return-property name="DormRoom" column="dorm_room" />
   <return-property name="ClassRoom" column="class_room" />
    </return>
    exec get_person_location :semester_id
  </sql-query>
</hibernate-mapping>

Hope this saves someone a few minutes.