C# - Multiple keys in JOIN LINQ Lambda Expressions

Asked

Viewed 635 times

2

I have an SQL as follows:

SELECT * FROM KY_AR_SOLICITACAO A
JOIN KY_AR_SOLICITACAO_ITEM B ON A.NF_ENTRADA = B.NF_ENTRADA AND A.SERIE_NF_ENTRADA = B.SERIE_NF_ENTRADA AND A.NOME_CLIFOR = B.NOME_CLIFOR

And in my Asp.net MVC project, I want to do this JOIN with more keys, but I’ve searched everything and I can’t find how to do this JOIN with multiple keys.. can anyone help me? What I have so far is this:

var itens = db.KY_AR_SOLICITACAO.Join(db.KY_AR_SOLICITACAO_ITEM,
                                              a => a.NF_ENTRADA,
                                              item => item.NF_ENTRADA,
                                              (a, item) => new { KY_AR_SOLICITACAO = a, KY_AR_SOLICITACAO_ITEM = item }).
                                              Where(c => c.KY_AR_SOLICITACAO.STATUS == 1);

That is returning me the following select (I made a profiler):

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[NF_ENTRADA] AS [NF_ENTRADA], 
    [Extent1].[NOME_CLIFOR] AS [NOME_CLIFOR], 
    [Extent1].[SERIE_NF_ENTRADA] AS [SERIE_NF_ENTRADA], 
    [Extent1].[OBSERVACAO] AS [OBSERVACAO], 
    [Extent1].[USUARIO_SOLICITACAO] AS [USUARIO_SOLICITACAO], 
    [Extent1].[DATA_SOLICITACAO] AS [DATA_SOLICITACAO], 
    [Extent1].[STATUS] AS [STATUS], 
    [Extent1].[USUARIO_CONFERENCIA] AS [USUARIO_CONFERENCIA], 
    [Extent1].[DATA_CONFERENCIA] AS [DATA_CONFERENCIA], 
    [Extent2].[NF_ENTRADA] AS [NF_ENTRADA1], 
    [Extent2].[NOME_CLIFOR] AS [NOME_CLIFOR1], 
    [Extent2].[SERIE_NF_ENTRADA] AS [SERIE_NF_ENTRADA1], 
    [Extent2].[ITEM_IMPRESSAO] AS [ITEM_IMPRESSAO], 
    [Extent2].[SUB_ITEM_TAMANHO] AS [SUB_ITEM_TAMANHO], 
    [Extent2].[CODIGO_ITEM] AS [CODIGO_ITEM], 
    [Extent2].[DESCRICAO_ITEM] AS [DESCRICAO_ITEM], 
    [Extent2].[QTDE_ITEM] AS [QTDE_ITEM], 
    [Extent2].[UNIDADE] AS [UNIDADE]
    FROM  [dbo].[KY_AR_SOLICITACAO] AS [Extent1]
    INNER JOIN [dbo].[KY_AR_SOLICITACAO_ITEM] AS [Extent2] ON [Extent1].[NF_ENTRADA] = [Extent2].[NF_ENTRADA]
    WHERE 1 = [Extent1].[STATUS]

I need it to stay that way (note on JOIN)

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[NF_ENTRADA] AS [NF_ENTRADA], 
    [Extent1].[NOME_CLIFOR] AS [NOME_CLIFOR], 
    [Extent1].[SERIE_NF_ENTRADA] AS [SERIE_NF_ENTRADA], 
    [Extent1].[OBSERVACAO] AS [OBSERVACAO], 
    [Extent1].[USUARIO_SOLICITACAO] AS [USUARIO_SOLICITACAO], 
    [Extent1].[DATA_SOLICITACAO] AS [DATA_SOLICITACAO], 
    [Extent1].[STATUS] AS [STATUS], 
    [Extent1].[USUARIO_CONFERENCIA] AS [USUARIO_CONFERENCIA], 
    [Extent1].[DATA_CONFERENCIA] AS [DATA_CONFERENCIA], 
    [Extent2].[NF_ENTRADA] AS [NF_ENTRADA1], 
    [Extent2].[NOME_CLIFOR] AS [NOME_CLIFOR1], 
    [Extent2].[SERIE_NF_ENTRADA] AS [SERIE_NF_ENTRADA1], 
    [Extent2].[ITEM_IMPRESSAO] AS [ITEM_IMPRESSAO], 
    [Extent2].[SUB_ITEM_TAMANHO] AS [SUB_ITEM_TAMANHO], 
    [Extent2].[CODIGO_ITEM] AS [CODIGO_ITEM], 
    [Extent2].[DESCRICAO_ITEM] AS [DESCRICAO_ITEM], 
    [Extent2].[QTDE_ITEM] AS [QTDE_ITEM], 
    [Extent2].[UNIDADE] AS [UNIDADE]
    FROM  [dbo].[KY_AR_SOLICITACAO] AS [Extent1]
    INNER JOIN [dbo].[KY_AR_SOLICITACAO_ITEM] AS [Extent2] ON [Extent1].[NF_ENTRADA] = [Extent2].[NF_ENTRADA] AND [Extent1].[SERIE_NF_ENTRADA] = [Extent2].[SERIE_NF_ENTRADA] AND [Extent1].[NOME_CLIFOR] = [Extent2].[NOME_CLIFOR]
    WHERE 1 = [Extent1].[STATUS]

1 answer

2


To inform more than one key use new { ... //suas chaves }, thus:

var itens = db.KY_AR_SOLICITACAO
          .Join(
               db.KY_AR_SOLICITACAO_ITEM,
               a => new
                       {
                           // Aqui você passa as chaves
                           a.NF_ENTRADA, a.SERIE_NF_ENTRADA, a.NOME_CLIFOR
                       },
               item => new
                       {
                           // Aqui você passa as chaves
                           item.NF_ENTRADA, item.SERIE_NF_ENTRADA, item.NOME_CLIFOR
                       },
               (a, item) => new { KY_AR_SOLICITACAO = a, KY_AR_SOLICITACAO_ITEM = item })
               .Where(c => c.KY_AR_SOLICITACAO.STATUS == 1);
  • It worked out man!! Thank you very much!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.