Multiple left joins in LINQ

Question

I am trying to convert a SQL query to LINQ and am having trouble with getting the syntax correct. My original (working) SQL query is:

 SELECT a.PersonnelNumber,
    a.LastName,
    a.FirstName,
    a.MiddleInitial,
    b.Title,
    b.Division,
    b.Section,
    b.Unit,
    d.PersonnelNumber AS SupervisorPersonnelNumber
FROM Person a
     JOIN Position b ON a.PositionID = b.PositionID
     LEFT JOIN Position c ON b.SupervisorPositionID = c.PositionID
     LEFT JOIN Person d ON c.PositionID = d.PositionID

I converted that into the following LINQ:

var query = from a in ctx.People
            from b in ctx.Positions.Where(b => a.PositionID == b.PositionID)
            from c in ctx.Positions.Where(c => b.SupervisorPositionID == c.PositionID).DefaultIfEmpty()
            from d in ctx.People.Where(d => c.PositionID == d.PositionID).DefaultIfEmpty()
            select new { 
                            a.PersonnelNumber, 
                            a.LastName, 
                            a.FirstName, 
                            a.MiddleInitial, 
                            b.Title, 
                            b.Division, 
                            b.Section, 
                            b.Unit, 
                            SupervisorPersonnelNumber = d.PersonnelNumber
                       };

This returned way more results than I was anticipating (20000+ instead of ~1100) so I looked at the generated SQL:

SELECT 
[Extent2].[PositionID] AS [PositionID], 
[Extent1].[PersonnelNumber] AS [PersonnelNumber], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[MiddleInitial] AS [MiddleInitial], 
[Extent2].[Title] AS [Title], 
[Extent2].[Division] AS [Division], 
[Extent2].[Section] AS [Section], 
[Extent2].[Unit] AS [Unit], 
[Extent4].[PersonnelNumber] AS [PersonnelNumber1]
FROM    [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Position] AS [Extent2] ON [Extent1].[PositionID] = [Extent2].[PositionID]
LEFT OUTER JOIN [dbo].[Position] AS [Extent3] ON [Extent2].[SupervisorPositionID] = [Extent3].[PositionID]
LEFT OUTER JOIN [dbo].[Person] AS [Extent4] ON ([Extent3].[PositionID] = [Extent4].[PositionID]) OR (([Extent3].[PositionID] IS NULL) AND ([Extent4].[PositionID] IS NULL))

The last line of this is what is causing my issue:

LEFT OUTER JOIN [dbo].[Person] AS [Extent4] ON ([Extent3].[PositionID] = [Extent4].[PositionID]) OR (([Extent3].[PositionID] IS NULL) AND ([Extent4].[PositionID] IS NULL))

I wasn't sure why the additional OR clause was added on and removing it returned the desired results.

In case it helps, the Position table has (effectively, though not enforced) a 1:1 relationship with Person and Position has a relationship to itself: PositionID is FK to SupervisorPositionID

CREATE TABLE [dbo].[Position](
[PositionID] [int] IDENTITY(1,1) NOT NULL,
[PositionNumber] [varchar](8) NULL,
[Title] [varchar](40) NULL,
[Division] [varchar](40) NULL,
[Section] [varchar](40) NULL,
[Unit] [varchar](40) NULL,
[SupervisorPositionID] [int] NULL,
)

CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[PersonnelNumber] [varchar](8) NOT NULL,
[LastName] [varchar](40) NULL,
[FirstName] [varchar](40) NULL,
[MiddleInitial] [char](1) NULL,
[PositionID] [int] NULL,
)

Why is OR (([Extent3].[PositionID] IS NULL) AND ([Extent4].[PositionID] IS NULL)) being appended to the end of this line and what can I do to fix it?

Answer 1

I don't have any tools at the moment to check if this will produce the required output, but i think it should be close enough:

            from a in Persons
            join b in Positions on a.PositionID equals b.PositionID
            join c in Positions on b.SupervisorPositionID equals c.PositionID into SupervisorsPositions
                from c in SupervisorsPositions.DefaultIfEmpty()
            join d in Persons on c.PositionID equals d.PositionID into PersonalNumbers
                from d in PersonalNumbers.DefaultIfEmpty()
            select new { 
                            a.PersonnelNumber, 
                            a.LastName, 
                            a.FirstName, 
                            a.MiddleInitial, 
                            b.Title, 
                            b.Division, 
                            b.Section, 
                            b.Unit, 
                            SupervisorPersonnelNumber = d.PersonnelNumber
                       }

This query will produce:

SELECT [t0].[PersonnelNumber], [t0].[LastName], [t0].[FirstName], [t0].[MiddleInitial], [t1].[Title], [t1].[Division], [t1].[Section], [t1].[Unit], [t3].[PersonnelNumber] AS [SupervisorPersonnelNumber]
FROM [Person] AS [t0]
INNER JOIN [Position] AS [t1] ON [t0].[PositionID] = ([t1].[PositionID])
LEFT OUTER JOIN [Position] AS [t2] ON [t1].[SupervisorPositionID] = ([t2].[PositionID])
LEFT OUTER JOIN [Person] AS [t3] ON ([t2].[PositionID]) = [t3].[PositionID]

By T-SQL language specification LEFT OUTER JOIN is equal to LEFT JOIN. INNER JOIN is equal to JOIN.

So that query produces the result that you require. See that answer for more information about Join types in T-SQL.

Answer 2

You need to change the setting on your DbContext. The property is UseDatabaseNullSemantics and you need to set it to false

context.Configuration.UseDatabaseNullSemantics = false;