NHibernate: What the heck is "clazz_"?

Being a reticent fellow, it isn't often that I blog twice in as many days, but I ran into an issue with NHibernate joined-subclasses and ISQLQuery twice in two different instances in the past two days.

After searching around a good deal for any information I could find about adding entities to SQL queries in NHibernate, I finally found something that pointed me in the right direction, and I thought I'd give it a post to hopefully assist any other unfortunate Hibernate users out there who may encounter this problem.

The Error

You can reproduce this error by creating a class inheritance like:

<class name="BaseEntity" table="BaseEntity">
  ...

  <joined-subclass name="FirstChildEntity" table="FirstChildeEntity">
    ...
  </joined-subclass>

  <joined-subclass name="SecondChildEntity" table="SecondChildeEntity">
    ...
  </joined-subclass>
</class>

Now when you run a SQL query like so:

Session.CreateSQLQuery(@"
select * 
from 
  BaseEntity be
  left join FirstBaseEntity fbe on be.Id = fbe.EntityId
  left join SecondBaseEntity sbe on be.Id = sbe.EntityId
").AddEntity(typeof(BaseEntity)).List<BaseEntity>();

We get the following error message.

clazz_

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: clazz_
Stack Trace:

[IndexOutOfRangeException: clazz_]
  System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) +2307341
  System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) +258
  NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) +33
  NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) +133
  NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name, ISessionImplementor session, Object owner) +13
  NHibernate.Loader.Loader.GetInstanceClass(IDataReader rs, Int32 i, ILoadable persister, Object id, ISessionImplementor session) +215
  NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) +78
  NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) +636
  NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) +285
  NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +756   NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +105
  NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) +236

The Research

The error message is not very helpful.  All we can really tell from the stack trace is that it's trying to do a field name lookup, but what field is "clazz_"?

If you are like me, you immediately pull open Google and run a search for anything about Nhibernate/Hibernate, clazz_, and createsqlquery.  One of the few useful results is this anotated source code on FishEye, from which we can see that Hibernate uses the "clazz_" string as a column discriminator.  Great!  But for what purpose?

Examining the code a little further, and reading the comments, we can discover that "clazz_" is a column added to the generated sql to differentiate between polymorphic classes, i.e. subclasses.  This is and old version, but it seems we're on the right trail.

A quick search on the Hibernate.org forums (which, for some reason, aren't indexed by Google) reveals some 8-10 threads concerning this very issue.  Sadly, it's not the good folks on the Hibernate project who are the most helpful in this case.  Thanks, however, to a fellow by the name of credmond, we can glean a little more info about this "clazz_" column.

To get any further, we're going to have to get our hands dirty.  Let's run a standard HQL query and see what kind of SQL it generates.

Session.CreateQuery("from BaseEntity").List();

The resulting SQL string looks something like this (with most of the fields removed for space):

select 
  entity0_.Id as Id27_, 
  [base entity fields],
  [first child entity fields],
  [second child entity fields],
  case 
    when entity0_1_.EntityId is not null then 1 
    when entity0_2_.EntityId is not null then 2 
    when entity0_.Id is not null then 0 
  end as clazz_ 
from 
  BaseEntity entity0_ 
  left outer join FirstChildEntity entity0_1_ on entity0_.Id=entity0_1_.EntityId 
  left outer join SecondChildEntity entity0_2_ on entity0_.Id=entity0_2_.EntityId

We can see here the case statement credmond spoke of.  My first question is: what happens if we change the order of the joined-subclasses in the mapping file?  The resultant SQL looks much the same, except for one important aspect: the aliases in the from clause are reversed!

from
  BaseEntity entity0_ 
  left outer join SecondChildEntity entity0_1_ on entity0_.Id=entity0_1_.EntityId
  left outer join FirstChildEntity entity0_2_ on entity0_.Id=entity0_2_.EntityId

So it appears that the number associated with each subclass is dependent upon the order of the subclasses in the mapping file.  Thus we find our (ugly) workaround.

The Workaround

We can get our code working (albeit, not very pretty) by adding the "clazz_" discriminator column manually to our SQL:

Session.CreateSQLQuery(@"
select *,
  case 
    when fbe.EntityId is not null then 1 
    when sbe.EntityId is not null then 2 
    else 0 
  end as clazz_
from 
  BaseEntity be
  left join FirstBaseEntity fbe on be.Id = fbe.EntityId
  left join SecondBaseEntity sbe on be.Id = sbe.EntityId
").AddEntity(typeof(BaseEntity)).List<BaseEntity>();

To make sure this doesn't break down the road, a comment should be added to the mapping file to convey the importance of maintaining the order of the joined-subclass descriptions.

While I don't like this solution, it's the simplest one I've found until the Hibernate team fixes what I consider to be a bug in their implementation of joined-subclasses.