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_.EntityIdWe 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.
