Dealing with NHibernate System.IndexOutOfRangeException

Whenever I call SQL Server stored procedures from an NHibernate (3.2) application, I generate proxy methods that look like this

IList<T> MyProcedure<T>(ISession session
  , [some parameters]
)
{
  IQuery sp = session.CreateSQLQuery(@"BEGIN
    EXECUTE MyProcedure
            :SomeParameter
END")
    .AddEntity(typeof(T));
  sp.SetParameter<[datatype]>("SomeParameter", SomeParameter);
  ...;
  sp.SetResultTransformer(Transformers.RootEntity);
  //sp.SetTimeout(timeout);
  return sp.List<T>();
}

and manually create the data class MyProcedureResult and the corresponding mapping class MyProcedure_Result.

The SP is invoked like this:

var result = Procedures.MyProcedure<MyProcedureResult>(session, [parameters]);

This usually works, but recently I experienced an exception when calling a specific SP:

[Data]

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: [Data]

Source Error: 

Stack Trace: 

[IndexOutOfRangeException: [Data]]
   System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) +5043194
   System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) +67
   NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Driver\NHybridDataReader.cs:363
   NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:236
   NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:195
   NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\AbstractType.cs:131
   NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:2511
   NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:988
   NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:943
   NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:875
   NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:334
   NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:468
   NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:246
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1590

[GenericADOException: could not execute query
[ BEGIN
    EXECUTE MyProcedure
            @p0
           ,@p1
END ]
[SQL: BEGIN
    EXECUTE MyProcedure
            @p0
           ,@p1
END]]
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1599
   NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1497
   NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1487
   NHibernate.Impl.StatelessSessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:376
   NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:353
   NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:363
   NHibernate.Impl.SqlQueryImpl.List() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SqlQueryImpl.cs:160

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.547

As you can see, NH expects the column name [Data], which does not exist in the result set, causing GetOrdinal() to throw an exception.

What’s going on here?

Well, the stored procedure returns a result set containing the column name Data, but NH tries to find the column [Data].

After stepping through the source code of NH 3.2, I claim to partially understand how this happens.

When the entities and mappings are initialized, NH calls connection.GetSchema(DbMetaDataCollectionNames.ReservedWords) to check whether column names are reserved words. If a column name is found to be a reserved word, the name is flagged to be quoted and/or aliased when used in SQL.

If, however, the column name occurs in the result set of an SP, aliasing cannot be applied, and quoting is not required.

Quoting using backticks (`name`) as proposed on SO did not help either.

After many experiments, my solution was to simply rename the columns in the SP (DataX), and add a column name mapping

map.Column("DataX");

to the class map.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: