Luiz C. Aquino
Luiz C. Aquino

Reputation: 1

How to use IN clause inside Left Join with Linq?

I'm refactoring a system and in the legacy there is a query that part of the script is like this in one of the left joins:

LEFT JOIN solucoes.sleccecentro_custo_empresa cce ON     
    cce.cod_centro_custo_empresa in (                                    
                                    cli.cod_centro_custo_empresa,      
                                    cdt.cod_centro_custo_empresa,      
                                    emu.cod_centro_custo_empresa,      
                                    vei.cod_centro_custo_empresa       
                                    )                                   
AND cce.cod_empresa_convenio = caa.cod_empresa_convenio 

I converted sql to linq and this part of the code looks like this:

join ccemp in Context.Set<CentroCustoEmpresasEntity>()
    on
    new
    {
        c1 = cli.IdCentroCustoEmpresa,
        c2 = cond.IdCentroCustoEmpresa,
        c3 = empu.IdCentroCustoEmpresa,
        c4 = veic.IdCentroCustoEmpresa,
        c5 = carg.EmpresaConveniadaId
    }
    equals new
    {
        c1 = ccemp.Id,
        c2 = ccemp.Id,
        c3 = ccemp.Id,
        c4 = ccemp.Id,
        c5 = ccemp.IdEmpresaConvenio
    }
    into
        CentroCustoEmpresasGroup
from
    ccemp in CentroCustoEmpresasGroup.DefaultIfEmpty()

The problem is that I can get a result, but it's not the same as SQL. I would like suggestions on how to work with IN inside the left join, I tried to find something, but without success!

Thank you very much!

Upvotes: 0

Views: 45

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

JOIN can be expressed by SelectMany, check original documentation: Complex Query Operators. Instead of IN you can use OR

...
from ccemp in Context.Set<CentroCustoEmpresasEntity>()
    .Where(ccemp => 
        cli.IdCentroCustoEmpresa  == ccemp.Id ||
        cond.IdCentroCustoEmpresa == ccemp.Id ||
        empu.IdCentroCustoEmpresa == ccemp.Id ||
        veic.IdCentroCustoEmpresa == ccemp.Id ||
        carg.EmpresaConveniadaId  == ccemp.Id
    )
    .Where(ccemp => carg.EmpresaConveniadaId == ccemp.IdEmpresaConvenio)
    .DefaultIfEmpty()
...

Upvotes: 1

Related Questions