Why doesn't my NHibernate request work with IndexOutOfRangeException?

Question

I use Fluent NHibernate, and in some cases my request fails with a System.IndexOutOfRangeException .

The problem seems to be related to the combination of using the name of the escaped column in the display and calling CreateSQLQuery().AddEntity() . It works fine if none of the columns require escaping or instead I 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() raises a System.IndexOutOfRangeException .

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

Or, if I changed the username mapping to Map(e => e.Username).Column("Username"); and changed the SQL query to SELECT No_, [E-mail Login] AS Username FROM Employees , it works great. But this will break the display of other places in my code that use CreateCriteria<Employee>() . And I cannot 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 using aliases for column names and using a result transformer. It is not perfect since it ignores my collation 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 problem?