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

Answer 1

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)