I have an SQL like this:
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 make this JOIN with more keys, but I have already looked for everything and I do not think 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);
What 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 you to stay that way (note in 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]