Multiple left unions in LINQ
I am trying to convert a SQL query to LINQ and I am having trouble setting the syntax correctly. My original (working) SQL query:
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 this to 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 more results than I expected (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 problem:
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 clause was added OR
and removing it returned the desired results.
In case that helps, the table Position
has (in fact, though not enforced) a 1: 1 s ratio Person
and Position
is relevant to itself: PositionID
is FK beforeSupervisorPositionID
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 it being OR (([Extent3].[PositionID] IS NULL) AND ([Extent4].[PositionID] IS NULL))
added to the end of this line and what can I do to fix it?
source to share
You need to change the setting to your DbContext. UseDatabaseNullSemantics property and you need to set it to false
context.Configuration.UseDatabaseNullSemantics = false;
source to share
I don't have the tools at the moment to test if this produces the desired result, 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]
From T-SQL Language Specification LEFT OUTER JOIN equals LEFT JOIN. INNER JOIN equals JOIN.
Thus, the query produces the result you want. See details for more information on join types in T-SQL.
source to share