I have the following exception seeming 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]
The odd part is a System.IndexOutOfRangeException: col_0_0_, which I could not understand why this was happening. I can update the message with some xml display if necessary.
Tpx
Update:
Mappings and metalization request 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"/> </class> </hibernate-mapping>
And request:
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[]>();
source share