NHibernate is producing SQL with a bad join

Question

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

Madeleinemadelena

Answer 1

I have implemented this join using a stored procedure. Hopefully NHibernate will fix this bug soon.

Madeleinemadelena

Answer 2

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)

Rettarettig

Thanks for the quick answer. I've tried your suggestion (and had come up with the same thing myself, just using .HasValue instead of != null) and it didn't help. – Madeleinemadelena

@MarkWithers: What happens if you completely remove that part from the Where clause? How is your mapping between WorkflowCaseAction and WorkflowCaseView? – Rettarettig

If I remove "|| x.CaseView.MooseUserId == userId" from the where clause it brings back actions without case records and uses three left outer joins as I would expect it to. – Madeleinemadelena

Answer 3

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.

Glavin