NHibernate produces bad join SQL

Question

I have an NHibernate Linq request that does not work as I expected.

The problem seems to be related to using a column with a null index from the left joined table in the where clause. This causes the connection to act as an internal connection.

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 created in this way looks like this (starting from the connection - you do not need to see all selected)

 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 causing the problem is line 5 of the above snippet. As you can see, NHibernate is trying to make an “old school” mix in my WorkflowCaseView. This causes the query to exclude other valid actions that do not have a CaseId in the WorkflowAction table.

Can someone explain why NHibernate writes this SQL, and how can I encourage it to create 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 HasMany, coming back the other way ...

EDIT. Doesn't seem to help

Answer 1

I implemented this connection using a stored procedure. I hope NHibernate will fix this error soon.

Answer 2

I think you need to change the Where clause to this:

 .Where(x => x.AssignedUser.Id == userId || (x.CaseView != null && x.CaseView.MooseUserId == userId)) 

With the current Where clause, you tell NHibernate that there will always be a CaseView , because you unconditionally access its properties. Based on this information, NHibernate optimizes your query from left outer join to inner join (which includes the "old school")

Answer 3

Try using Fluent NHibernate. Something like the following should lead you to the correct 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 that I have a special class that extends FluentSessionManager.GetSession (), where I can call it directly using a simple helper class or on page by page. Your FluentSessionManager setup can be significantly different. But ultimately in ".CreateCriteria () ..." your code and mine should match. Assuming "WorkflowAction" is the table to which the query is called.