I've been playing around with the fancy new native SQL query support in NHibernate, specifically using stored procedures to query data. I've been trying to load a class with a component using a stored procedure, however I get an ADO.NET IndexOutOfRange exception when NHibernate tries to load the component property; which makes sense since the underlying datareader does not contain a column named: FieldCo33_1_0_. It seems NHibernate cannot resolve user aliases for components; which might very well be intentional. Essentially NHibernate tried to lookup the alias using 'FieldResponse' rather than 'FieldResponse.FieldComments' in its userProvidedAliases hashtable (DefaultEntityAliases.GetUserProvidedAliases() method).

Interestingly enough, if I change the alias in the stored procedure from FieldComments to FieldCo33_1_0_, it works just fine and even populates the component. Of course this is not a very workable solution.

Is it possible to use a sproc to load a class with a component? Its probably just something simple I overlooked...



NHibernate version:
1.2 Beta 1

Mapping document:

<class name='EventDetail' mutable='false' lazy='false'>
<id name='ErrorId' type='Int32' unsaved-value='-1'>
<generator class='assigned' />
</id>
<component name='FieldResponse' class='FieldResponse'>
<property name='FieldComments' type='string' />
</component>
<loader query-ref='EventDetail'/>
</class>

<sql-query name='EventDetail'>
<return alias='ed' class='EventDetail'>
<return-property name='ErrorId' column='ErrorId' />
<return-property name='FieldResponse.FieldComments' column='FieldComments' />
</return>
exec uspSelectArbitrationDetail ?
</sql-query>

Code between sessionFactory.openSession() and session.close():
EventDetail detail = Session.Load<EventDetail>(262489);

Full stack trace of any exception that occurs:

System.IndexOutOfRangeException: FieldCo33_1_0_
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Driver\NHybridDataReader.cs:line 362
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Type\NullableType.cs:line 247
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Type\NullableType.cs:line 206
at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Type\AbstractType.cs:line 128
at NHibernate.Type.ComponentType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Type\ComponentType.cs:line 654
at NHibernate.Loader.Loader.Hydrate(IDataReader rs, Object id, Object obj, ILoadable persister, ISessionImplementor session, String[][] suffixedPropertyColumns) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Loader\Loader.cs:line 1037
at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, Type instanceClass, EntityKey key, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Loader\Loader.cs:line 936
at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Loader\Loader.cs:line 893
at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Loader\Loader.cs:line 827
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Loader\Loader.cs:line 295
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Loader\Loader.cs:line 427
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Loader\Loader.cs:line 183
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in c:\net\nhibernate-1.2.0.Beta1\nhibernate\src\NHibernate\Loader\Loader.cs:line 1646

Name and version of the database you are using:
SQL Server 2005

The generated SQL (show_sql=true):
exec uspSelectArbitrationDetail @p0; @p0 = '262489'

Use <return-column> (example from a NHibernate test case):

Thanks! That solved my problem. For some reason I thought the return-column element was used for something else. I changed my mapping document to this:

<sql-query name='EventDetail'>
<return alias='ed' class='EventDetail'>
<return-property name='ErrorId' column='ErrorId' />
<return-property name="FieldResponse">
<return-column name="FieldComments" />
<return-column name="FieldResponseDate" />
</return-property>
</return>
exec uspSelectArbitrationDetail ?
</sql-query>

I did notice that property declaration order in the mapping file is important.