LINQ 中的多个左联接

我正在尝试将 SQL 查询转换为 LINQ，但无法正确获取语法。我原来的（工作的）SQL 查询是：

 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

我将其转换为以下 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
                       };

这返回的结果比我预期的要多（20000+ 而不是 ~1100），所以我查看了生成的 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))

最后一行是导致我的问题的原因：

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

我不确定为什么要额外OR添加子句并删除它返回了所需的结果。

如果有帮助的话，Position表与（有效，但未强制执行）具有 1:1 关系Person and Position与自身有关系：PositionID是 FK 到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))被附加到该行的末尾，我可以做什么来修复它？

您需要更改 DbContext 上的设置。该物业是使用DatabaseNull语义 http://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbcontextconfiguration.usedatabasenullsemantics(v=vs.113).aspx你需要将其设置为 false

context.Configuration.UseDatabaseNullSemantics = false;