LINQ中的多个左联接

问题描述：

我正在尝试将SQL查询转换为LINQ，并且在获取正确的语法时遇到了麻烦.我原来的(有效的)SQL查询是:

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

我将其转换为以下LINQ:

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
                       };

返回的结果比我预期的要多(20000+，而不是〜1100)，所以我查看了生成的SQL:

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))

我不确定为什么要添加附加的 OR 子句并将其删除会返回所需的结果.

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

在可能的情况下， Position 表与(

Person >和 Position 与自身的关系: PositionID 是 SupervisorPositionID

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,
)

为什么将 OR(([Extent3].[PositionID] IS NULL)AND([Extent4].[PositionID] IS 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?

答

我目前没有任何工具可以检查这是否会产生所需的输出，但是我认为它应该足够接近:

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
                       }

此查询将产生:

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]

通过 T-SQL语言规范LEFT OUTER JOIN等于LEFT JOIN.内部联接等于联接.

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

这样，该查询将产生所需的结果.请参阅该答案，以获取有关T-SQL.

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

相关推荐