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'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; })