Join C# EF6 lambda Expression

Asked

Viewed 85 times

1

I’m having a problem, I’m doing a Join with C# with EF6, I have two unrelated tables Invest and Encarteiramento and need to make a Join in Invest where agency and account is equal to, agency and account of Encarteiramento and the consultant’s enrollment is the same as the person logged in. The consultant’s enrollment I already receive normally. Follow my code.

var resultado = _context.Invest.Join(_context.Encarteiramento,
                inv => new { agencia = inv.AGENCIA, conta = inv.CONTA },
                enc => new { agencia = enc.Agencia, conta = enc.Conta },
                (inv, enc) => new { inv, enc })
                .Where(result => result.enc.Matricula == matriculaConsultor);

SQL Generated

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[AGENCIA] AS [AGENCIA], 
    [Extent1].[Vlr_Evento] AS [Vlr_Evento], 
    [Extent1].[CONTA] AS [CONTA], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[TIP_CLIENTE] AS [TIP_CLIENTE], 
    [Extent2].[CPF] AS [CPF], 
    [Extent2].[DATA] AS [DATA], 
    [Extent2].[Agencia] AS [AGENCIA1], 
    [Extent2].[Conta] AS [CONTA1], 
    [Extent2].[AG_PRINC] AS [AG_PRINC], 
    [Extent2].[CONTA_PRINC] AS [CONTA_PRINC], 
    [Extent2].[CONSULTOR] AS [CONSULTOR], 
    [Extent2].[Matricula] AS [Matricula], 
    [Extent2].[EQUIPE_RESPONSAVEL] AS [EQUIPE_RESPONSAVEL], 
    [Extent2].[EQUIPE_MESA] AS [EQUIPE_MESA], 
    [Extent2].[DIR_REG_AG_PRINC] AS [DIR_REG_AG_PRINC], 
    [Extent2].[GER_REG_AG_PRINC] AS [GER_REG_AG_PRINC]
    FROM  [dbo].[Investfacil] AS [Extent1]
    INNER JOIN [dbo].[Encarteiramento] AS [Extent2] ON (([Extent1].[AGENCIA] = [Extent2].[Agencia]) OR (([Extent1].[AGENCIA] IS NULL) AND ([Extent2].[Agencia] IS NULL))) AND (([Extent1].[CONTA] = [Extent2].[Conta]) OR (([Extent1].[CONTA] IS NULL) AND ([Extent2].[Conta] IS NULL)))
    WHERE [Extent2].[Matricula] = @p__linq__0

When I remove the ([Extent1]. [AGENCIA] IS NULL) AND ([Extent2].[Agencia] IS NULL). I can get the results correctly, and I don’t know why it is generating the query in this format. How I can solve this problem?

Thanks in advance for your attention.

c# entity-framework-6 join

  • agency and account are with which typing? as far as I’m concerned, this check is normal on Ef, is a check for null

  • @Lucas Miranda, was exactly that, in my bank they were null, which did not make sense because this field can not be null. I changed the table and now it’s working.

1 answer

0

I managed to solve, in my case the problem was in the bank because these columns had to be not null and were as null and so error in searching, I changed all to not null and now it’s working.

Browser other questions tagged c# entity-framework-6 join

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