NHibernate - NHibernate.Exceptions.GenericADOException: could not execute query

Question

I have the following exception occurring seemingly at random:

    NHibernate.Exceptions.GenericADOException: could not execute query
    [ select businesspr0_.BusinessProcessID as col_0_0_, businesspr0_.ProcessNumber as col_1_0_, businesspr1_.Name as col_2_0_, businesspr0_.DateCreated as col_3_0_, actor2_.DisplayName as col_4_0_, usergroup3_.Name as col_5_0_, processele5_.Name as col_6_0_ from BusinessProcess businesspr0_ inner join BusinessProcess businesspr1_ on businesspr0_.DefinitionID=businesspr1_.BusinessProcessID inner join Actor actor2_ on businesspr0_.ActorCreatedID=actor2_.ActorID left outer join UserGroup usergroup3_ on businesspr0_.UserGroupCreatedID=usergroup3_.UserGroupID left outer join Actor usergroup3_1_ on usergroup3_.UserGroupID=usergroup3_1_.ActorID inner join ActiveElement activeelem4_ on businesspr0_.BusinessProcessID=activeelem4_.BusinessProcessID inner join ProcessElement processele5_ on activeelem4_.ProcessElementID=processele5_.ProcessElementID inner join ProcessEmployees processemp6_ on businesspr0_.BusinessProcessID=processemp6_.BusinessProcessID inner join Actor actor7_ on processemp6_.ActorID=actor7_.ActorID inner join UserInGroups users8_ on actor7_.ActorID=users8_.UserGroupID inner join AppUser user9_ on users8_.UserID=user9_.UserID inner join Actor user9_1_ on user9_.UserID=user9_1_.ActorID where businesspr0_.IsDefinition=0 and (businesspr0_.Finished is null) and @p0=user9_.UserID ]
      Name:userID - Value:32771
    [SQL: select businesspr0_.BusinessProcessID as col_0_0_, businesspr0_.ProcessNumber as col_1_0_, businesspr1_.Name as col_2_0_, businesspr0_.DateCreated as col_3_0_, actor2_.DisplayName as col_4_0_, usergroup3_.Name as col_5_0_, processele5_.Name as col_6_0_ from BusinessProcess businesspr0_ inner join BusinessProcess businesspr1_ on businesspr0_.DefinitionID=businesspr1_.BusinessProcessID inner join Actor actor2_ on businesspr0_.ActorCreatedID=actor2_.ActorID left outer join UserGroup usergroup3_ on businesspr0_.UserGroupCreatedID=usergroup3_.UserGroupID left outer join Actor usergroup3_1_ on usergroup3_.UserGroupID=usergroup3_1_.ActorID inner join ActiveElement activeelem4_ on businesspr0_.BusinessProcessID=activeelem4_.BusinessProcessID inner join ProcessElement processele5_ on activeelem4_.ProcessElementID=processele5_.ProcessElementID inner join ProcessEmployees processemp6_ on businesspr0_.BusinessProcessID=processemp6_.BusinessProcessID inner join Actor actor7_ on processemp6_.ActorID=actor7_.ActorID inner join UserInGroups users8_ on actor7_.ActorID=users8_.UserGroupID inner join AppUser user9_ on users8_.UserID=user9_.UserID inner join Actor user9_1_ on user9_.UserID=user9_1_.ActorID where businesspr0_.IsDefinition=0 and (businesspr0_.Finished is null) and @p0=user9_.UserID]
 ---> System.IndexOutOfRangeException: col_0_0_
       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.Hql.Ast.ANTLR.Loader.QueryLoader.GetResultColumnOrRow(Object[] row, IResultTransformer resultTransformer, IDataReader rs, 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)
       --- End of inner exception stack trace ---
       at DotExe.TiCat.DomainModel.Repository.ProcessModel.BusinessProcessDAO.GetProcessForUserGroups(Int32 userID) in D:\Projects\TiCatRNIDS\DotExe.TiCat4\DotExe.TiCat.DomainModel.Repository\ProcessModel\BusinessProcessDAO.cs:line 98
       at DotExe.TiCat4.UseCaseRepository.TicketProcess.TicketQueues.RefreshQueue() in D:\Projects\TiCatRNIDS\DotExe.TiCat4\DotExe.TiCat4\UseCaseRepository\TicketProcess\TicketQueues.cs:line 128

Odd part is this System.IndexOutOfRangeException: col_0_0_ which I could not figure out why is happening. I can update post with some xml mapping if necessary.

Tnx

Update:

Mappings and the query throwing ex:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DotExe.TiCat.DomainModel" namespace="DotExe.TiCat.DomainModel.Process">

  <class name="BusinessProcess" table="BusinessProcess">
    <id name="BusinessProcessID">
      <generator class="hilo"/>
    </id>
    <property name = "Name" length="1024" type="string" not-null ="false" />
    <property name = "Description" length="5000" type="string" not-null ="false" />
    <property name = "ProcessNumber" length="500" type="string" not-null ="false" />
    <property name = "CommentOnSolve" length="5000" type="string" not-null ="false" />
    <property name="IsDefinition" type="bool"/>
    <property name="PlaningTimeToSpend" type="long"/>
    <property name="SpendTime" type="long"/>
    <property name="DateCreated" type="DateTime" not-null="false"/>
    <property name="PlanToStart" type="DateTime" not-null="false"/>
    <property name="PlanToFinish" type="DateTime" not-null="false"/>
    <property name="Started" type="DateTime" not-null="false"/>
    <property name="Finished" type="DateTime" not-null="false"/>
    <property name="CustomerRequestCallBack" type="bool" not-null="false"/>
    <property name="EscalationTimeLimitInPercents" type="int"/>
    <property name="IsEscalated" type="bool"/>
    <property name="IsInfinite" type="bool"/>
    <many-to-one name="WorkingWeek" class="WorkingWeek" column="WorkingWeekID" not-null="false" lazy="false"/>
    <many-to-one name="Priority" class="ProcessPriority" column="PriorityID" not-null="false" lazy="false"/>
    <many-to-one name="UserCreated" class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorCreatedID" not-null="false" lazy="false"/>
    <many-to-one name="UserSolved" class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorSolvedID" not-null="false" lazy="false"/>
    <many-to-one name="GroupCreated" class="DotExe.TiCat.DomainModel.Actors.UserGroup" column="UserGroupCreatedID" not-null="false" lazy="false"/>
    <many-to-one name="GroupSolved" class="DotExe.TiCat.DomainModel.Actors.UserGroup" column="UserGroupSolvedID" not-null="false" lazy="false"/>
    <many-to-one name="Definition" class="BusinessProcess" column="DefinitionID" cascade="none" />      
    <set name="Elements" lazy="false" cascade="save-update">
      <key column="BusinessProcessID"/>
      <one-to-many class="ProcessElement"/>
    </set>
    <set name="ActiveElements" table="ActiveElement" lazy="false" cascade="save-update">
      <key column="BusinessProcessID"/>
      <many-to-many class="ProcessElement" column="ProcessElementID"/>
    </set>
    <set name="ProcessConsumers" table="ProcessConsumers" lazy="false" cascade="save-update">
      <key column="BusinessProcessID"/>
      <many-to-many class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID"/>
    </set>
    <set name="ProcessEmployees" table="ProcessEmployees" lazy="false">
      <key column="BusinessProcessID"/>
      <many-to-many class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID"/>
    </set>
    <set name="ProcessSupervisors" table="ProcessSupervisors" lazy="false">
      <key column="BusinessProcessID"/>
      <many-to-many class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID"/>
    </set>
    <set name="Flows" lazy="false" cascade="save-update">
      <key column="BusinessProcessID"/>
      <one-to-many class="SequenceFlow"/>
    </set>
    <set name="PriorityTimeRules" lazy="false" cascade="save-update">
      <key column="BusinessProcessID"/>
      <one-to-many class="DotExe.TiCat.DomainModel.BusinessRules.PriorityTimeRule"/>
    </set>
  </class>

  <class name="ProcessElement" table="ProcessElement">
    <id name="ProcessElementID">
      <generator class="hilo"/>
    </id>
    <property name = "Name" length="1024" type="string" not-null ="true" />
    <property name="IsDefinition" type="bool"/>
    <property name="IsState" type="bool"/>

    <many-to-one class="BusinessProcess" name="MemberOf" lazy="false" cascade="save-update" column="BusinessProcessID"/>
    <many-to-one class="ProcessElement" name="Definition" column="DefinitionID"/>

    <joined-subclass name="Activity">
      <key column="ActivityID"/>
      <property name = "Name" length="1024" type="string" not-null ="true" />
      <property name = "Description" length="4000" type="string" not-null ="false" />
      <set name="States" >
        <key column="ActivityID"/>
        <one-to-many class="ActivityState"/>
      </set>

      <joined-subclass name="UserTask" table="UserTask">
        <key column="UserTaskID"/>
        <property name="PlaningTimeToSpend" type="int"/>
        <property name="SpendTime" type="int"/>
        <property name="PlanToStart" type="DateTime" not-null="false"/>
        <property name="PlanToFinish" type="DateTime" not-null="false"/>
        <property name="Started" type="DateTime" not-null="false"/>
        <property name="Finished" type="DateTime" not-null="false"/>
        <set name="Comments">
          <key column="UserTaskID"/>
          <one-to-many class="TaskComment"/>
        </set>
        <set name="UserTaskEmployees">
          <key column="UserTaskID"/>
          <many-to-many class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID"/>
        </set>
        <set name="UserTaskSupervisors">
          <key column="UserTaskID"/>
          <many-to-many class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID"/>
        </set>
        <set name="Priorities">
          <key column="UserTaskID"/>
          <one-to-many class="UserTaskPriority"/>
        </set>
        <set name="AssignmentRules">
          <key column="UserTaskID"/>
          <one-to-many class="DotExe.TiCat.DomainModel.BusinessRules.AssignmentRule"/>
        </set>
        <many-to-one class="DotExe.TiCat.DomainModel.UseCase.ClientApplicationConfig" name="Application" column="ApplicationConfigID"/>
        <many-to-one class="UserTaskPriority" name="TaskPriority" column="TaskPriorityID"/>
      </joined-subclass>

      <joined-subclass name="SendTask">
        <key column="SendTaskID"></key>
        <many-to-one name="EmailAccount" class="DotExe.TiCat.DomainModel.Config.EmailAccount" column="EmailAccountID" lazy="false"></many-to-one>
        <many-to-one name="Template" class="DotExe.TiCat.DomainModel.Templates.Template" column="TemplateID" lazy="false"></many-to-one>
        <set name="Actors" lazy="false">
          <key column="SendTaskID"></key>
          <many-to-many class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID"></many-to-many>
        </set>
        <set name="EmailMessages">
          <key column="SendTaskID"></key>
          <one-to-many class="DotExe.TiCat.DomainModel.Email.EmailMessage"/>
        </set>
      </joined-subclass>
    </joined-subclass>

    <joined-subclass name="Gateway">
      <key column="GatewayID"/>
      <joined-subclass name="ExclusiveGateway">
        <key column="ExclusiveGatewayID"/>
      </joined-subclass>
    </joined-subclass>

    <joined-subclass name="Event">
      <key column="EventID"></key>
      <property name="ExecutionTime" type="DateTime" not-null="false"></property>

      <joined-subclass name="StartEvent">
        <key column="StartEventID"></key>
      </joined-subclass>

      <joined-subclass name="EndEvent">
        <key column="EntEventID"></key>
      </joined-subclass>
    </joined-subclass>
  </class>
  <class name="ActivityState" table="ActivityState">
    <id name="ActivityStateID">
      <generator class="hilo"/>
    </id>
    <property name = "Name" length="1024" type="string" not-null ="true" />
    <property name = "Description" length="4000" type="string" not-null ="false" />
    <many-to-one name="StateOf" class="Activity" column="ActivityID"/>
    <property name="IsEndState" type="bool"/>
    <property name="IsStartState" type="bool"/>
    <property name="IsPauseState" type="bool"/>
    <property name="IsProcessSerializationState" type="bool"/>
  </class>
  <class name="SequenceFlow" table ="SequenceFlow">
    <id name="SequenceFlowID">
      <generator class="hilo"/>
    </id>
    <many-to-one class="ProcessElement" name="Source" column="SourceID" lazy="false" cascade="save-update"/>
    <many-to-one class="ProcessElement" name="Target" column="TargetID" lazy="false" cascade="save-update"/>
    <many-to-one name="Process" class="BusinessProcess" column="BusinessProcessID" cascade="save-update"/>
  </class>
  <class name="UserTaskPriority" table ="UserTaskPriority">
    <id name="UserTaskPriorityID">
      <generator class="hilo"/>
    </id>
    <property name = "Name" length="1024" type="string" not-null ="true" />
  </class>
  <class name="TaskComment" table ="TaskComment">
    <id name="TaskCommentID">
      <generator class="hilo"/>
    </id>
    <property name = "Comment" length="4000" type="string" not-null ="true" />
    <many-to-one name="Task" class="ProcessElement" column="UserTaskID"/>
    <many-to-one name="Actor" class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID"/>
    <many-to-one name="Process" class="BusinessProcess" column="BusinessProcessID"/>
    <many-to-one name="Group" class="DotExe.TiCat.DomainModel.Actors.UserGroup" column="UserGroupID"/>
    <property name="CommentTime" type="DateTime" not-null="false" />
  </class>
  <class name="TransitionLog">
    <id name="TransitionLogID">
      <generator class="hilo"/>
    </id>
    <property name="LogDate" type="DateTime"/>
    <property name="TaskStarted" type="DateTime" not-null ="false" />
    <property name="TaskEnded" type="DateTime" not-null ="false" />
    <property name="TimeSpend" type="int" not-null ="false" />
    <set name="SendToActors" table="SendToActorLog" lazy="false">
      <key column="TransitionLogID"/>
      <many-to-many class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID"/>
    </set>
    <many-to-one name="Actor" class="DotExe.TiCat.DomainModel.Actors.Actor" column="ActorID" lazy="false"/>
    <many-to-one name="Group" class="DotExe.TiCat.DomainModel.Actors.UserGroup" column="UserGroupID"/>
    <many-to-one name="FromElement" class="ProcessElement" column="FromElementID" lazy="false"/>
    <many-to-one name="ToElement" class="ProcessElement" column="ToElementID"/>
    <many-to-one name="Process" class="BusinessProcess" column="BusinessProcessID"/>
    <many-to-one name="LogType" class="LogType" column="LogTypeID" lazy="false"/>
    <property name="Comment" length="4000" type="string" not-null ="false" />
  </class>
  <class name="LogType">
    <id name="LogTypeID"/>
    <property name = "Name" length="1024" type="string" not-null ="false" />
    <property name = "DisplayName" length="1024" type="string" not-null ="false" />
  </class>

  <class name="ProcessPriority">
    <id name="ProcessPriorityID"/>
    <property name = "Name" length="1024" type="string" not-null ="false" />
    <property name = "DisplayName" length="1024" type="string" not-null ="false" />
  </class>

  <class name="WorkingWeek">
    <id name="WorkingWeekID">
      <generator class="hilo"/>
    </id>
    <property name="MondayFrom" type="DateTime" not-null="true"/>
    <property name="MondayTo" type="DateTime" not-null="true"/>
    <property name="TuesdayFrom" type="DateTime" not-null="true"/>
    <property name="TuesdayTo" type="DateTime" not-null="true"/>
    <property name="WednesdayFrom" type="DateTime" not-null="true"/>
    <property name="WednesdayTo" type="DateTime" not-null="true"/>
    <property name="ThursdayFrom" type="DateTime" not-null="true"/>
    <property name="ThursdayTo" type="DateTime" not-null="true"/>
    <property name="FridayFrom" type="DateTime" not-null="true"/>
    <property name="FridayTo" type="DateTime" not-null="true"/>
    <property name="SaturdayFrom" type="DateTime" not-null="true"/>
    <property name="SaturdayTo" type="DateTime" not-null="true"/>
    <property name="SundayFrom" type="DateTime" not-null="true"/>
    <property name="SundayTo" type="DateTime" not-null="true"/>
    <property name="Is24x7" type="bool"/>
    <property name="Name" type="string" length="256" not-null="true"/>
    <!--<set name="BusinessProcesses" cascade="none" lazy="false">
      <key column="WorkingWeekID"/>
      <one-to-many class="BusinessProcess"/>
    </set>-->

  </class>

</hibernate-mapping>

And the query:

IList<object[]> result =
                        session.CreateQuery(" select bp.BusinessProcessID, bp.ProcessNumber, def.Name as Definition," +
                                            " bp.DateCreated, actor.DisplayName as UserCreated," +
                                            " group.Name as GroupCreated, task.Name as ActiveTask " +
                                            " from BusinessProcess bp " +
                                            " join bp.Definition as def " +
                                            " join bp.UserCreated as actor " +
                                            " left join bp.GroupCreated as group " +
                                            " join bp.ActiveElements as task" +
                                            " join bp.ProcessEmployees as res" +
                                            " join res.Users as users " +
                                            " where bp.IsDefinition = false " +
                                            " and bp.Finished is null" +
                                            " and :userID = users.ActorID").SetParameter("userID", userID).List<object[]>();

Answer 1

I don't know the reason for this. But there is something odd in the mappings: Activity has the property "Name", which is already mapped in the base class. This may lead to index out of range. There are probably more problems around.

Be very careful with sets like "Actors" which are many-to-many and do not specify a table name. There is a high risk of defining another "Actors" set which will get conflicting table definitions.

Unrelated for sure: some sets should be inverse, because there is a reference from the element to the parent.

Answer 2

I've gotten this error when I tried to map a null value to a non-nullable property (i.e. an integer). make sure that either you make sure that the sql value is non-nullable, or just make the c# property nullable (public virtual int? NumberOfPoints { get; set; })