How to use the IN clause in Lambda?

Question

A doubt arose that I thought would be easy, but the content I found could not understand.

Well, here’s the thing: I have the tables

"Client"

and

"Errosproducao_client"

where they relate form 1 to N, ie 1 error can affect N Customers.

So I need to do in lambda exactly the SQL query below:

select * from Cliente 
where Id_Cliente in (select CodCliente 
                     from ErrosProducao_Cliente 
                     where codErro = 1)

I know I can solve this problem with a Join, however would like to find out how is done using IN.

Unlike the question quoted below in the answer my problem wanted to realize the IN directly through a subSelect.

Answer 1

Do something like this.

using (var ctx = new stackoverflowEntities())
{

    var qrInSubCategoria = ctx.Cliente
        .Where(C => ctx.ErrosProducao_Cliente.Any(EC => EC.CodCliente== C.CodCliente && C.codErro == 1));
}

Or

using (var ctx = new stackoverflowEntities())
{
    var listint = ctx.ErrosProducao_Cliente
        .Where(EC => EC.codErro == 1)
        .Select(EC => EC.CodCliente)
        .ToList();

    var qrInSubCategoria = ctx.Cliente
        .Where(C => listint.Contains(C.CodCliente));

}

Or still using . Distinct() in your client code.

using (var ctx = new stackoverflowEntities())
{
    var listint = ctx.ErrosProducao_Cliente
        .Where(EC => EC.codErro == 1)
        .Select(EC => EC.CodCliente)
        .Distinct()
        .ToList();

    var qrInSubCategoria = ctx.Cliente
        .Where(C => listint.Contains(C.CodCliente));

}

Answer 2

// pegar os códigos do clientes e jogar na lista
var codigos = context.ErrosProducao_Cliente.Where(e => e.codErro == 1).Select(e => e.CodCliente).ToList();
// aqui seleciona os clientes que contenham na lista gerada acima
var clientes = context.Clientes.Any(c => codigos.Contains(c.codCliente)).ToList();