Question:
A doubt arose that I thought would be easy, but the content I found I couldn't understand.
Good is the following: I have the tables
"Client"
and
"ErrorsProduction_Customer"
where they are related in a 1 to N way, that is, 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
, but I would like to find out how it is done using IN.
Unlike the question mentioned below in the answer, my problem wanted to perform the IN
directly through a subSelect
.
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));
}