NHibernate is producing SQL with a bad join
I have an NHibernate Linq query which isn't working how I would expect.
The problem seems to come from using a nullable int column from a left joined table in the where clause. This is causing the join to act like an inner join.
var list = this.WorkflowDiaryManager.WorkflowActionRepository.All
.Fetch(x => x.CaseView)
.Fetch(x => x.WorkflowActionType)
.ThenFetchMany(x => x.WorkflowActionPriorityList)
.Where(x => x.AssignedUser.Id == userId || x.CaseView.MooseUserId == userId)
The SQL produced by this looks like (from the join onwards - you don't need to see all the selects)
from Kctc.WorkflowAction workflowac0_
left outer join Kctc.WorkflowCaseView workflowca1_ on workflowac0_.CaseId=workflowca1_.CaseId
left outer join Kctc.WorkflowActionType workflowac2_ on workflowac0_.WorkflowActionTypeId=workflowac2_.WorkflowActionTypeId
left outer join Kctc.WorkflowActionPriority workflowac3_ on workflowac2_.WorkflowActionTypeId=workflowac3_.WorkflowActionTypeId
,Kctc.WorkflowCaseView workflowca4_
where workflowac0_.CaseId=workflowca4_.CaseId
and (workflowac0_.AssignedUser=@p0 or workflowca4_.[MooseUserId]=@p1);
@p0 = 1087 [Type: Int32 (0)],
@p1 = 1087 [Type: Int32 (0)]
So the part that is causing the problem is line 5 of the snippet above. As you can see, NHibernate is trying to do an 'old-school' join on my WorkflowCaseView View. This causes the query to exclude otherwise valid actions which do not have a CaseId in the WorkflowAction table.
Could anyone explain why NHibernate is writing this SQL, and how I might encourage it to produce a better query?
Thanks!
Important bits from WorkflowActionMap
Table("Kctc.WorkflowAction");
Id(x => x.Id).GeneratedBy.Identity().Column("WorkflowActionId");
References(x => x.WorkflowActionType).Column("WorkflowActionTypeId").Unique();
References(x => x.CompletedBy).Column("CompletedBy");
References(x => x.CaseView).Column("CaseId").Not.Update().Unique();
References(x => x.AssignedUser).Column("AssignedUser");
Important bits from WorkflowCaseViewMap
Table("Kctc.WorkflowCaseView");
Id(x => x.Id).Column("CaseId");
Map(x => x.MooseUserId).Nullable();
Looking at this, I wonder if I should have a HasMany going back the other way...
EDIT. Doesn't seem to help
I think you need to change your Where clause to this:
.Where(x => x.AssignedUser.Id == userId ||
(x.CaseView != null && x.CaseView.MooseUserId == userId))
With your current Where clause you tell NHibernate that there always will be a CaseView , because you unconditionally access its properties. Based on this information NHibernate optimizes your query from a left outer join to an inner join (which the "old-school" join is)
I have implemented this join using a stored procedure. Hopefully NHibernate will fix this bug soon.
Try using Fluent NHibernate. Something like the following should get you in the right ball park:
var List<WorkflowAction> = FluentSessionManager.GetSession().CreateCriteria<WorkflowAction>()
.SetFetchMode("CaseView", FetchMode.Eager)
.SetFetchMode("WorkflowActionType", FetchMode.Eager)
.SetFetchMode("WorkflowActionPriorityList", FetchMode.Eager)
.CreateAlias("AssignedUser", "au")
.CreateAlias("CaseView", "cv")
.Add(Expression.Or(Expression.Eq("au.Id", userId), Expression.Eq("cv.MooseUserId", userId)))
.List<WorkflowAction>();
Keep in mind, I have a special class that extends FluentSessionManager.GetSession() where I can call it directly with a simple helper class or on a page by page basis. Your setup of the FluentSessionManager may be considerably different. But ultimately at ".CreateCriteria()..." your code and mine should match. Assuming "WorkflowAction" is the table the query is being called against.
链接地址: http://www.djcxy.com/p/10476.html