How to use the IN clause in Lambda?

Question

How to use the IN clause in Lambda?

Navigation

#1 by (3 votes)
#2 by (2 votes)

4

There was a doubt that I thought would be easy, but the content I found I could not understand.

Good is as follows: I have the tables

"Customer"

and

"ErrorsProducao_Ciente"

where they relate 1 to N, that is, 1 error can affect N Customers.

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

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 , but I would like to find out how it is done using IN.

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

sql c# entity-framework

asked by anonymous 12.09.2016 / 19:43

2 answers

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();

12.09.2016 / 19:57

How to get the HOST IP through the domain in PYTHON? How to receive external data via XML

score 3 · Accepted Answer

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 even 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));

}