c# - 如何使用 EF 编写此 LEFT JOIN 查询

Question

我们正在使用 Entity Framework，我们想执行一个简单的 LEFT JOIN。

事实上，这是我们希望在 LINQ (Queryable) 中拥有的 SQL:

SELECT 
     cbl.ID as ClaimBatchLine_ID
    ,cbl.PurchasePrice
    ,c.*
    ,ic.DueDate
    ,ic.Reference
FROM ClaimBatchLine cbl
INNER JOIN Claim c ON c.ID = cbl.CLaim_ID
LEFT JOIN InvoiceClaim ic ON ic.ID = c.ID
WHERE cbl.ClaimBatch_ID = @claimBatchId
ORDER BY cbl.ID
OFFSET (@recordsPerPage*@page) ROWS
FETCH NEXT @recordsPerPage ROWS ONLY

我们想出的是:

from cbl in ClaimBatchLines where cbl.ClaimBatch_ID == 1
from c in Claims where c.ID == cbl.Claim_ID
from ic in InvoiceClaims.DefaultIfEmpty() where ic.ID == c.ID
select new {cbl, c, ic.Reference}

这会产生以下 SQL。

SELECT [t0].[ID],
     [t0].[ClaimBatch_ID],
     [t0].[Claim_ID],
     [t0].[PurchasePrice],
     [t1].[ID] AS [ID2],
     [t1].[ClaimType_ID],
     [t1].[Debtor_ID],
     [t1].[CustomerContractRevision_ID],
     [t1].[Date],
     [t1].[CreatedOn],
     [t1].[GrossAmount],
     [t1].[OpenAmount],
     [t1].[IsProcessedByOpenAmountCalculator],
     [t1].[RowVersion],
     [t2].[Reference] AS [Reference]
FROM [ClaimBatchLine] AS [t0]
    CROSS JOIN [Claim] AS [t1]
                   LEFT OUTER JOIN [InvoiceClaim] AS [t2] ON 1 = 1
WHERE([t2].[ID] = [t1].[ID])
    AND ([t1].[ID] = [t0].[Claim_ID])
    AND ([t0].[ClaimBatch_ID] = @p0);

它产生相同的结果集。那太好了。但是，如您所见，LEFT OUTER JOIN [InvoiceClaim] AS [t2] ON 1 = 1 并不是我们想要的。我希望它能将其转换为 LEFT JOIN InvoiceClaim ic ON ic.ID = c.ID。

我们做错了什么吗？或者 LINQ to SQL 只是次优(就性能而言)并且无法理解我们想要什么。

编辑:在 LINQPad 中，这会产生一些不错的查询

from cbl in ClaimBatchLines
join c in Claims on cbl.Claim_ID equals c.ID
join ic in InvoiceClaims on c.ID equals ic.ID into g
from e in g.DefaultIfEmpty()
where cbl.ClaimBatch_ID == 1
select new {cbl, c, e.Reference}

即

-- Region Parameters
DECLARE @p0 INT= 1;
-- EndRegion
SELECT [t0].[ID],
     [Columns left out for brevity]
     [t2].[Reference] AS [Reference]
FROM [ClaimBatchLine] AS [t0]
    INNER JOIN [Claim] AS [t1] ON [t0].[Claim_ID] = [t1].[ID]
    LEFT OUTER JOIN [InvoiceClaim] AS [t2] ON [t1].[ID] = [t2].[ID]
WHERE [t0].[ClaimBatch_ID] = @p0;

但是当像这样添加分页功能时:

(from cbl in ClaimBatchLines
join c in Claims on cbl.Claim_ID equals c.ID
join ic in InvoiceClaims on c.ID equals ic.ID into g
from e in g.DefaultIfEmpty()
where cbl.ClaimBatch_ID == 1
select new {cbl, c, e.Reference})
.OrderBy(a => a.cbl.ID)
.Skip(0 * 15000)
.Take(15000)

它产生了这个“怪物”:

-- Region Parameters
DECLARE @p0 INT= 1;
DECLARE @p1 INT= 0;
DECLARE @p2 INT= 15000;
-- EndRegion
SELECT [t4].[ID],
     [Columsn left out for brevity...]
FROM
(
   SELECT ROW_NUMBER() OVER(ORDER BY [t3].[ID]) AS [ROW_NUMBER],
        [Columsn left out for brevity...]
   FROM
   (
      SELECT [t0].[ID],
            [Columsn left out for brevity...]
      FROM [ClaimBatchLine] AS [t0]
          INNER JOIN [Claim] AS [t1] ON [t0].[Claim_ID] = [t1].[ID]
          LEFT OUTER JOIN [InvoiceClaim] AS [t2] ON [t1].[ID] = [t2].[ID]
   ) AS [t3]
   WHERE [t3].[ClaimBatch_ID] = @p0
) AS [t4]
WHERE [t4].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t4].[ROW_NUMBER];

更糟的是。当我不是通过 LINQpad 而是在使用 EF 存储库的代码中执行相同的 LINT-To-EF 时，我得到了这个更大的怪物:

SELECT [Project5].[ID] AS [ID],
     [Columns left out for brevity...]
     [Project5].[Reference] AS [Reference]
FROM
(
   SELECT [Extent1].[ID] AS [ID],
        [Extent1].[Claim_ID] AS [Claim_ID],
        [Extent1].[ClaimBatch_ID] AS [ClaimBatch_ID],
        [Extent1].[PurchasePrice] AS [PurchasePrice],
        [Join4].[Id1] AS [ID1],
        [Join4].[ClaimType_ID] AS [ClaimType_ID],
        [Join4].[Debtor_ID] AS [Debtor_ID],
        [Join4].[CustomerContractRevision_ID] AS [CustomerContractRevision_ID],
        [Join4].[Date] AS [Date],
        [Join4].[GrossAmount] AS [GrossAmount],
        [Join4].[OpenAmount] AS [OpenAmount],
        [Join4].[CreatedOn] AS [CreatedOn],
        [Join4].[IsProcessedByOpenAmountCalculator] AS [IsProcessedByOpenAmountCalculator],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN '2X'
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN '2X0X'
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN '2X1X'
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN '2X2X'
            ELSE '2X3X'
        END AS [C1],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS BIT)
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN [Join4].[IsAppeared]
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN CAST(NULL AS BIT)
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN CAST(NULL AS BIT)
        END AS [C2],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS TINYINT)
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS TINYINT)
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN [Join4].[AdjustmentClaimReason_ID]
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN CAST(NULL AS TINYINT)
        END AS [C3],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN [Join4].[User_ID]
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN CAST(NULL AS INT)
        END AS [C4],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN [Join4].[CostClaimAnnouncement_ID]
        END AS [C5],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS DECIMAL(19, 4))
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS DECIMAL(19, 4))
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN CAST(NULL AS DECIMAL(19, 4))
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN [Join4].[DiscountFactor]
        END AS [C6],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS DATETIME2)
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS DATETIME2)
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN CAST(NULL AS DATETIME2)
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN [Join4].[DiscountValidTo]
        END AS [C7],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN [Join4].[AppliedDiscountAdjustmentClaim_ID]
        END AS [C8],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN CAST(NULL AS INT)
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN [Join4].[ExpiredDiscountAdjustmentClaim_ID]
        END AS [C9],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS VARCHAR(1))
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS VARCHAR(1))
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN CAST(NULL AS VARCHAR(1))
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN CAST(NULL AS VARCHAR(1))
            ELSE [Join4].[Reference]
        END AS [C10],
        CASE
            WHEN((NOT(([Join4].[C11] = 1)
                    AND ([Join4].[C11] IS NOT NULL)))
                AND (NOT(([Join4].[C12] = 1)
                       AND ([Join4].[C12] IS NOT NULL)))
                AND (NOT(([Join4].[C13] = 1)
                       AND ([Join4].[C13] IS NOT NULL)))
                AND (NOT(([Join4].[C14] = 1)
                       AND ([Join4].[C14] IS NOT NULL))))
            THEN CAST(NULL AS DATETIME2)
            WHEN(([Join4].[C14] = 1)
                AND ([Join4].[C14] IS NOT NULL))
            THEN CAST(NULL AS DATETIME2)
            WHEN(([Join4].[C12] = 1)
                AND ([Join4].[C12] IS NOT NULL))
            THEN CAST(NULL AS DATETIME2)
            WHEN(([Join4].[C11] = 1)
                AND ([Join4].[C11] IS NOT NULL))
            THEN CAST(NULL AS DATETIME2)
            ELSE [Join4].[DueDate]
        END AS [C11],
        [Extent7].[Reference] AS [Reference]
   FROM [dbo].[ClaimBatchLine] AS [Extent1]
       INNER JOIN
   (
      SELECT [Extent2].[Id] AS [Id1],
            [Columns left out for brevity...]
      FROM [dbo].[Claim] AS [Extent2]
          LEFT OUTER JOIN
      (
         SELECT [Extent3].[Id] AS [Id],
               [Extent3].[CostClaimAnnouncement_ID] AS [CostClaimAnnouncement_ID],
               [Extent3].[DiscountFactor] AS [DiscountFactor],
               [Extent3].[DiscountValidTo] AS [DiscountValidTo],
               [Extent3].[AppliedDiscountAdjustmentClaim_ID] AS [AppliedDiscountAdjustmentClaim_ID],
               [Extent3].[ExpiredDiscountAdjustmentClaim_ID] AS [ExpiredDiscountAdjustmentClaim_ID],
               CAST(1 AS BIT) AS [C1]
         FROM [dbo].[CostClaim] AS [Extent3]
      ) AS [Project1] ON [Extent2].[Id] = [Project1].[Id]
          LEFT OUTER JOIN
      (
         SELECT [Extent4].[Id] AS [Id],
               [Extent4].[IsAppeared] AS [IsAppeared],
               CAST(1 AS BIT) AS [C1]
         FROM [dbo].[InterestClaim] AS [Extent4]
      ) AS [Project2] ON [Extent2].[Id] = [Project2].[Id]
          LEFT OUTER JOIN
      (
         SELECT [Extent5].[Id] AS [Id],
               [Extent5].[AdjustmentClaimReason_ID] AS [AdjustmentClaimReason_ID],
               [Extent5].[User_ID] AS [User_ID],
               CAST(1 AS BIT) AS [C1]
         FROM [dbo].[AdjustmentClaim] AS [Extent5]
      ) AS [Project3] ON [Extent2].[Id] = [Project3].[Id]
          LEFT OUTER JOIN
      (
         SELECT [Extent6].[Id] AS [Id],
               [Extent6].[Reference] AS [Reference],
               [Extent6].[DueDate] AS [DueDate],
               CAST(1 AS BIT) AS [C1]
         FROM [dbo].[InvoiceClaim] AS [Extent6]
      ) AS [Project4] ON [Extent2].[Id] = [Project4].[Id]
   ) AS [Join4] ON [Extent1].[Claim_ID] = [Join4].[Id1]
       LEFT OUTER JOIN [dbo].[InvoiceClaim] AS [Extent7] ON [Join4].[Id1] = [Extent7].[Id]
   WHERE 1 = [Extent1].[ClaimBatch_ID]
) AS [Project5]
ORDER BY [Project5].[ID] ASC
OFFSET 0 ROWS FETCH NEXT 15000 ROWS ONLY;

这到底是怎么回事!在 LINQpad 上抓取最初看起来不错。但是生产代码中的最终查询简直太可怕了!也许这些查询适用于一些简单的应用程序。在更多记录中查询 500k 条记录时，这对我来说不太好。我将坚持使用纯 SQL 表值函数而不是使用 LINQ。真可惜。

Answer 1

试试这个方法:

from cbl in ClaimBatchLines 
join c in Claims on c.ID equals cbl.Claim_ID
join ic in InvoiceClaims on ic.ID equals c.ID into g
from e in g.DefaultIfEmpty()
where cbl.ClaimBatch_ID == 1
select new {cbl, c, e.Reference}

有关如何在 linq 中执行左连接的更多信息，请查看此 link