Entonces ... EF6 está creando una consulta terriblemente ineficiente. Tengo una consulta que va contra una fuente de datos con tres tipos diferentes de direcciones. Tengo una lista de identificadores de direcciones que son posibles duplicados del nuevo que un usuario está tratando de usar. Idealmente, me gustaría que esta consulta verificara si alguno de los identificadores de dirección está en el conjunto dado de identificadores proporcionados. Actualmente esta consulta:

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

Se está convirtiendo en esto:

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

¿Cuál es la mejor manera de hacer que EF genere una mejor consulta aquí? Sería bueno si pudiera hacer:

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)
2
Jereme 16 dic. 2016 a las 00:39

2 respuestas

La mejor respuesta

Como señaló @Cory, Contains el método de extensión se traduce a IN en SQL, por lo que debe usarlo en lugar de Any, que se traduce a EXIST:

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

Y también debe usar el método de extensión OfType para obtener solo entidades SIFTEscalationMetadata

4
octavioccl 15 dic. 2016 a las 21:55

De su ejemplo de código, asumo lo siguiente.

_tickets es una secuencia de elementos de una clase, digamos un Ticket. y cada elemento de su secuencia tiene una propiedad MetaData. El valor devuelto de MetaData puede ser o no un SIFTEscalationMetaData. (¿Es esto cierto? ¿O cada metadato es un SIFTEscalationMetaData?)

Cuando el valor de la propiedad MetaData es SIFTEscalationMetaData, está seguro de que la propiedad Ticket.MetaData tiene al menos otras tres propiedades no nulas: Address, AddressEntered y CleanedAddress. Además, está seguro de que estas tres propiedades no devuelven NULL y que las tres tienen un ID de propiedad.

Además, también tiene una secuencia de adiciones, donde cada elemento es del mismo tipo que la propiedad Id. No sé el tipo de Id, pero supongamos que es IdType, probablemente un int o una cadena o similar

Aparentemente, no solo desea esos Tickets de su secuencia _tickets donde la propiedad MetaData es un SIFTEscalationMetaData, y al menos uno de los valores de Id de Ticket.MetaData.Address, Ticket.MetaData.AddressEntered o Ticke.MetaData.CleanedAddress está en la colección de addesses.

IEnumerable<Ticket> result = _tickets
    // first remember the ticket and convert the MetaData
    // to either null or a SIFTEscalationMetadata
    .Select(t => new
    {
        Ticket = t,
        MetaData = t.Metadata as SIFTEscalationMetadata,
    })
    // now take only those tickets where the metadata is not null
    .Where(t => t.MetaData != null)
    // and select from the remaining tickets the Ids you want to check
    .Select(t => new
    {
        Ticket = t.Ticket,
        Ids = new IdType[]
        {
            t.MetaData.Address.Id,
            t.MetaDate.AddressEntered.Id,
            t.MetaData.CleanedAddress.Id,
        },
     })
     // now take only those items where the intersection of the Ids and 
     // addesses has any elements
     .Where(t => addess.Interset(t.Ids).Any())
     .Select(t => t.Ticket);

La optimización está en la selección intermedia de tipos anónimos. Los tipos anónimos se convertirán en variables SQL. La conversión a SIFTEscalationMetaData se realiza solo una vez por ticket. La verificación de si alguno de los ID está en las adiciones se realiza solo una vez y solo para los tickets que tienen los metadatos correctos.

0
Harald Coppoolse 16 dic. 2016 a las 13:24