none
Avoid SubQuerys Code First RRS feed

  • Question

  • Hello,

    I have the diagram attached and i want to obtains this query via Linq/Code First due to better performance:

    SELECT     dbo.Mensajes.MensajesId, dbo.Trazas.TrazasId AS Expr1, dbo.Trazas.NombreTraza, dbo.Mercados.NombreMercados, dbo.Mercados.Id, 
                          dbo.Mensajes.NombreMensaje, dbo.Mensajes.MercadoId, dbo.Trazas.FechaEntrada
    FROM         dbo.Mensajes INNER JOIN
                          dbo.Mercados ON dbo.Mensajes.MercadoId = dbo.Mercados.Id INNER JOIN
                          dbo.TrazasMensajes ON dbo.Mensajes.MensajesId = dbo.TrazasMensajes.TrazasId INNER JOIN
                          dbo.Trazas ON dbo.TrazasMensajes.MensajesId = dbo.Trazas.TrazasId

    but via linq:

    from m in context.Mercados
    from n in m.Mensajes
    from o in n.Trazas
    select new {a=m,b=n,c=o}

    I obtain:

    SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[NombreMercados] AS [NombreMercados], 
    [Extent2].[MensajesId] AS [MensajesId], 
    [Extent2].[NombreMensaje] AS [NombreMensaje], 
    [Extent2].[MercadoId] AS [MercadoId], 
    [Join2].[TrazasId1] AS [TrazasId], 
    [Join2].[NombreTraza] AS [NombreTraza], 
    [Join2].[FechaEntrada] AS [FechaEntrada]
    FROM   [dbo].[Mercados] AS [Extent1]
    INNER JOIN [dbo].[Mensajes] AS [Extent2] ON [Extent1].[Id] = [Extent2].[MercadoId]
    INNER JOIN  (SELECT [Extent3].[TrazasId] AS [TrazasId2], [Extent4].[TrazasId] AS [TrazasId1], [Extent4].[NombreTraza] AS [NombreTraza], [Extent4].[FechaEntrada] AS [FechaEntrada]
    FROM  [dbo].[TrazasMensajes] AS [Extent3]
    INNER JOIN [dbo].[Trazas] AS [Extent4] ON [Extent4].[TrazasId] = [Extent3].[MensajesId] ) AS [Join2] ON [Extent2].[MensajesId] = [Join2].[TrazasId2]

    wich is worst in performace than the first one, 

    The strange thing is that this query (ignoring "Trazas" table) is correct:

    from m in context.Mercados
    from n in m.Mensajes
    from o in n.Trazas
    select new {a=m,b=n}

    SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[NombreMercados] AS [NombreMercados], 
    [Extent2].[MensajesId] AS [MensajesId], 
    [Extent2].[NombreMensaje] AS [NombreMensaje], 
    [Extent2].[MercadoId] AS [MercadoId]
    FROM   [dbo].[Mercados] AS [Extent1]
    INNER JOIN [dbo].[Mensajes] AS [Extent2] ON [Extent1].[Id] = [Extent2].[MercadoId]
    INNER JOIN [dbo].[TrazasMensajes] AS [Extent3] ON [Extent2].[MensajesId] = [Extent3].[TrazasId]

    but when i select the "Trazas" table the subquery appears, how can i obtain the first of all via linq/code First avoiding subquerys? 

    Thanks,

    Tuesday, October 16, 2012 7:16 AM

All replies

  • Hello Eric,

    Instead of using "from m in context.Mercados
    from n in m.Mensajes
    from o in n.Trazas" ,

    Can u try using linq query using "join" keywords like,

    var result = from m in context.Mercados
                 join men in context.Mensajes on m.Id equals men.MercadoID
                 ....
                 ....
    where  // write your condition here
    select // select custom object

    By writting linq query this way should generate SQL query as u described beginning of your post.

    regards,

    Dharmesh Solanki


                      



    • Edited by dmsolanki Tuesday, October 16, 2012 12:57 PM
    Tuesday, October 16, 2012 12:06 PM
  • Thanks Dharmesh

    But how can i do a inner join between two tables with a many to many relationship?, i only have 3 classes (Mercados, Trazas, Mensajes) but i have not a class for "MensajesTrazas" this is automatically created by CF.

    Tuesday, October 16, 2012 2:36 PM