Why is my NHibernate query failing with an IndexOutOfRangeException?

Question

I am using Fluent NHibernate and in certain cases my query is failing with a System.IndexOutOfRangeException.

It seems that the issue has to do with the combination of using escaped column name(s) in the mapping and calling CreateSQLQuery().AddEntity(). It works fine if none of the columns need escaping or if I instead use CreateCriteria<Employee>().

public class Employee {
    public virtual string EmployeeNumber { get; set; }
    public virtual string Username { get; set; }
}

public class EmployeeMapping : ClassMap<Employee> {
    public EmployeeMapping() {
        Table("Employees");
        Id(e => e.EmployeeNumber)
            .Column("No_");
        Map(e => e.Username)
            .Column("`E-mail Login`"); // Note the escaped column name
    }
}

public class SqlRepository {
    ...
    public IList<Employee> ListEmployees() {
        using (ISession session = _sessionBuilder.GetSession()) {
            return session
                .CreateSQLQuery("SELECT No_, [E-mail Login] FROM Employees")
                .AddEntity(typeof(Employee))
                .List<Employee>();
        }
    }
}

Calling ListEmployees() results in a System.IndexOutOfRangeException.

However, if I change the CreateSQLQuery().AddEntity() call to CreateCriteria<Employee>(), it works fine. But my actual SQL is more complex so I don't think that will work for me.

Or, if I change the Username mapping to Map(e => e.Username).Column("Username"); and change the SQL query to SELECT No_, [E-mail Login] AS Username FROM Employees, it works fine. But this would break the mapping for other places in my code that do use CreateCriteria<Employee>(). And I can't change the table schema at the moment.

Why is this failing? Do you have any other suggestions besides what I mentioned? Thanks.

I am using Fluent NHibernate 1.0, NHibernate 2.1.0.4000 and SQL Server 2005.

Here is the stack trace:

 NHibernate.ADOException was unhandled
   Message="could not execute query\r\n[ SELECT No_, [E-mail Login] FROM Employees ]\r\n[SQL: SELECT No_, [E-mail Login] FROM Employees]"
   Source="NHibernate"
   SqlString="SELECT No_, [E-mail Login] FROM Employees"
   StackTrace:
     at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
     at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
     at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)
     at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters)
     at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
     at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
     at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters)
     at NHibernate.Impl.SqlQueryImpl.List[T]()
     at NHibernateTest.Core.SqlRepository.ListEmployees() in C:\dev\NHibernateTest\NHibernateTest\SqlRepository.cs:line 14
     at NHibernateTest.Console.Program.Main(String[] args) in C:\dev\NHibernateTest\NHibernateTest.Console\Program.cs:line 8
     at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
     at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
     at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
     at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
     at System.Threading.ThreadHelper.ThreadStart()
   InnerException: System.IndexOutOfRangeException
     Message="[E-mail Login]"
     Source="System.Data"
     StackTrace:
    at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
    at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
    at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name)
    at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
    at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
    at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
    at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session)
    at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session)
    at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)
    at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session)
    at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)
    at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
    at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
    at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
     InnerException:

Answer 1

I got it to work by using aliases for the column names and using a result transformer. It's not ideal since it ignores my mapping and requires setting an alias for each column. But it works for now.

public class SqlRepository {
    ...
    public IList<Employee> ListEmployees() {
        using (ISession session = _sessionBuilder.GetSession()) {
            return session
                .CreateSQLQuery(@"
                    SELECT No_ AS EmployeeNumber, [E-mail Login] AS Username 
                    FROM Employees")
                .AddScalar("EmployeeNumber", NHibernateUtil.String)
                .AddScalar("Username", NHibernateUtil.String)
                .SetResultTransformer(Transformers.AliasToBean<Employee>())
                .List<Employee>();
        }
    }
}

Any better solutions? Could there be a bug in (Fluent) NHibernate causing the original issue?