c# - 如何让 EF6 生成高效的 in(...) 查询

Question

所以... EF6 正在创建一个非常低效的查询。我有一个针对具有三种不同类型地址的数据源的查询。我有一个地址 ID 列表，这些 ID 可能与用户尝试使用的新地址重复。理想情况下，我希望此查询检查几个地址 ID 中的任何一个是否在给定的一组提供的 ID 中。目前这个查询:

return await _tickets.Where(t =>
    t.Metadata is SIFTEscalationMetadata && (
        addesses.Any(a => a == (t.Metadata as SIFTEscalationMetadata).Address.Id) ||
        addesses.Any(a => a == (t.Metadata as SIFTEscalationMetadata).AddressEntered.Id) ||
        addesses.Any(a => a == (t.Metadata as SIFTEscalationMetadata).CleanedAddress.Id))).ToArrayAsync();

变成这样:

SELECT 
    [Project1].[TicketId] AS [TicketId], 
    [Project1].[TicketType] AS [TicketType], 
    [Project1].[Opened] AS [Opened], 
    [Project1].[Closed] AS [Closed], 
    [Project1].[Modified] AS [Modified], 
    [Project1].[EscalationStatusText] AS [EscalationStatusText], 
    [Project1].[QualificationStatusText] AS [QualificationStatusText], 
    [Project1].[ProductsText] AS [ProductsText], 
    [Project1].[Cancelled] AS [Cancelled], 
    [Project1].[CancellationReason_Id] AS [CancellationReason_Id], 
    [Project1].[CreatedBy_Id] AS [CreatedBy_Id], 
    [Project1].[Metadata_Id] AS [Metadata_Id], 
    [Project1].[NotesContainer_Id] AS [NotesContainer_Id]
    FROM ( SELECT 
        [Extent1].[TicketId] AS [TicketId], 
        [Extent1].[TicketType] AS [TicketType], 
        [Extent1].[Opened] AS [Opened], 
        [Extent1].[Closed] AS [Closed], 
        [Extent1].[Modified] AS [Modified], 
        [Extent1].[EscalationStatusText] AS [EscalationStatusText], 
        [Extent1].[QualificationStatusText] AS [QualificationStatusText], 
        [Extent1].[ProductsText] AS [ProductsText], 
        [Extent1].[Cancelled] AS [Cancelled], 
        [Extent1].[CancellationReason_Id] AS [CancellationReason_Id], 
        [Extent1].[CreatedBy_Id] AS [CreatedBy_Id], 
        [Extent1].[Metadata_Id] AS [Metadata_Id], 
        [Extent1].[NotesContainer_Id] AS [NotesContainer_Id], 
        CASE WHEN ([Extent2].[TicketMetadataID] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '2X0X' END AS [C1]
        FROM  [dbo].[Tickets] AS [Extent1]
        LEFT OUTER JOIN [dbo].[TicketMetadata] AS [Extent2] ON ([Extent2].[Discriminator] = N'SIFTEscalationMetadata') AND ([Extent1].[Metadata_Id] = [Extent2].[TicketMetadataID])
    )  AS [Project1]
    WHERE ([Project1].[C1] LIKE '2X0X%') AND (( EXISTS (SELECT 
        1 AS [C1]
        FROM   (SELECT 
            486524 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
        UNION ALL
            SELECT 
            486525 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
        UNION ALL
            SELECT 
            486526 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]
        UNION ALL
            SELECT 
            508376 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]
        UNION ALL
            SELECT 
            508377 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable5]
        UNION ALL
            SELECT 
            508378 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable6]) AS [UnionAll5]
        LEFT OUTER JOIN  (SELECT 
            [Extent3].[Address_Id] AS [Address_Id], 
            '2X0X' AS [C1]
            FROM [dbo].[TicketMetadata] AS [Extent3]
            WHERE ([Extent3].[Discriminator] = N'SIFTEscalationMetadata') AND ([Project1].[Metadata_Id] = [Extent3].[TicketMetadataID]) ) AS [Project8] ON 1 = 1
        WHERE [UnionAll5].[C1] = (CASE WHEN ([Project8].[C1] LIKE '2X0X%') THEN [Project8].[Address_Id] END)
    )) OR ( EXISTS (SELECT 
        1 AS [C1]
        FROM   (SELECT 
            486524 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable7]
        UNION ALL
            SELECT 
            486525 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable8]
        UNION ALL
            SELECT 
            486526 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable9]
        UNION ALL
            SELECT 
            508376 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable10]
        UNION ALL
            SELECT 
            508377 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable11]
        UNION ALL
            SELECT 
            508378 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable12]) AS [UnionAll10]
        LEFT OUTER JOIN  (SELECT 
            [Extent4].[AddressEntered_Id] AS [AddressEntered_Id], 
            '2X0X' AS [C1]
            FROM [dbo].[TicketMetadata] AS [Extent4]
            WHERE ([Extent4].[Discriminator] = N'SIFTEscalationMetadata') AND ([Project1].[Metadata_Id] = [Extent4].[TicketMetadataID]) ) AS [Project16] ON 1 = 1
        WHERE [UnionAll10].[C1] = (CASE WHEN ([Project16].[C1] LIKE '2X0X%') THEN [Project16].[AddressEntered_Id] END)
    )) OR ( EXISTS (SELECT 
        1 AS [C1]
        FROM   (SELECT 
            486524 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable13]
        UNION ALL
            SELECT 
            486525 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable14]
        UNION ALL
            SELECT 
            486526 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable15]
        UNION ALL
            SELECT 
            508376 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable16]
        UNION ALL
            SELECT 
            508377 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable17]
        UNION ALL
            SELECT 
            508378 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable18]) AS [UnionAll15]
        LEFT OUTER JOIN  (SELECT 
            [Extent5].[CleanedAddress_Id] AS [CleanedAddress_Id], 
            '2X0X' AS [C1]
            FROM [dbo].[TicketMetadata] AS [Extent5]
            WHERE ([Extent5].[Discriminator] = N'SIFTEscalationMetadata') AND ([Project1].[Metadata_Id] = [Extent5].[TicketMetadataID]) ) AS [Project24] ON 1 = 1
        WHERE [UnionAll15].[C1] = (CASE WHEN ([Project24].[C1] LIKE '2X0X%') THEN [Project24].[CleanedAddress_Id] END)
    ))) 

让 EF 在这里生成更好查询的最佳方法是什么？如果它能做到就好了:

SELECT ...
WHERE Address_Id in(486524, 486525, 486526, 508376, 508377, 508378)
      OR AddressEntered_Id in(486524, 486525, 486526, 508376, 508377, 508378)
      OR CleanedAddress_Id in(486524, 486525, 486526, 508376, 508377, 508378)

Answer 1

正如@Cory 指出的，Contains 扩展方法在 SQL 中被转换为 IN，因此您应该使用它而不是被转换的 Any到 EXIST:

return await _tickets.OfType<SIFTEscalationMetadata>()
                     .Where(t =>addesses.Contains(t.Address.Id) ||
                               addesses.Contains(t.AddressEntered.Id) ||
                               addesses.Contains(t.CleanedAddress.Id)).ToArrayAsync();

而且您还应该使用 OfType 扩展方法来仅获取 SIFTEscalationMetadata 实体