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?

Why don't you use [join operator](http://msdn.microsoft.com/ru-ru/library/bb311040.aspx)? — Ivan Zub, Aug 14 '14 at 18:56
I'm open to any suggestions. Do you have an example? I only did it this way because this got me closer to my desired results than any of the other ways I tried. — Grouch, Aug 14 '14 at 18:58
What if you try: `ctx.People.Where(d => c.PositionID == d.PositionID.Value).DefaultIfEmpty()` ? — Magnus, Aug 14 '14 at 19:09

Aducci · Answer 1 · 2014-08-14T19:22:14.033

1

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;

edited Aug 14 '14 at 19:22

answered Aug 14 '14 at 19:06

Aducci

26,101
8
63
67

No luck. This generated the same SQL and same results. – Grouch Aug 14 '14 at 19:08
Setting this property to False produced the same results. I also tried to set the `Nullable` property of `SupervisorPositionID` to `False` in the .edmx and that did not work either. – Grouch Aug 14 '14 at 19:32
Since the `SupervisorPositionID` is an `INT` the "null" values are coming through as zeroes. Perhaps that is part of the issue? – Grouch Aug 14 '14 at 19:44

score 1 · Answer 2 · edited May 23 '17 at 12:11

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.

This produces the same results. – Grouch Aug 14 '14 at 19:22 — Grouch, Aug 14 '14 at 19:22

Multiple left joins in LINQ

2 Answers2

Linked