Lambda, Where com subconsulta

Question

I need a consult like that on the lambda

    SELECT  ClienteId  --Aqui eu tenho varios campos do Cliente e estagioprocesso
    FROM    Cliente
    WHERE   ((SELECT  TOP (1) EP
                             FROM  estagioprocesso 
                             WHERE     (estagioprocesso.ClienteID = Cliente.ClienteId)
                             ORDER BY estagioprocesso.ID DESC)= 2)

That is, I check in the table Etapaprocess which is the last EP of this client (order by ID DESC) I take only 1 (top 1) and I check this EP if it is equal to the number I want return.

I want to bring all customer steps that the latest step has EP = id (2).

I tried to:

int id = 2;
var EP = db.EtapaProcess
       .Include(t => t.Cliente)
       .OrderByDescending(t => t.EP).Take(1).Where(x => x.EP == id)
       .ToList();

But it doesn’t work. Ex: There are 188 records that have already passed the EP=2, but that are with EP=2 only 5 records and with the above query returns me 0

Answer 1

It is quite wrong your query. From what I understand of her, do the following:

var EP = db.EtapaProcess
   .Include(t => t.Cliente)
   .Where(x => x.EP == ep) // ep é uma variável que viria de algum lugar.
   .OrderByDescending(t => t.Id)
   .GroupBy(t => t.ClienteId);

Answer 2

from what I understand, you want to bring all the customer steps that the latest step has the EP = id (2).

In this case, you will need to group by customer, to then make the comparison.

var epClientes = db.EtapaProcess
    .Include(etapa => etapa.Cliente)
    .GroupBy(etapa => etapa.ClienteID)
    .Where(grupo => grupo
        .OrderByDescending(etapa => etapa.ID)
        .Select(etapa => etapa.EP)
        .FirstOfDefault() == 2);

var EP = new List<EtapaProcess>();
foreach (var epCliente in epClientes)
{
    EP.AddRange(epCliente);
}

if you only want customers...

var clientes = db.EtapaProcess
    .Include(etapa => etapa.Cliente) // acredito que este include seja desnecessário.
    .GroupBy(etapa => etapa.Cliente)
    .Where(grupo => grupo
        .OrderByDescending(etapa => etapa.ID)
        .Select(etapa => etapa.EP)
        .FirstOfDefault() == 2)
    .Select(grupo => grupo.Key);

UPDATING

I believe you do not need to make a link to the customer table to get the expected result, you can use a query as below:

DECLARE @ep AS INT;
SET @ep = 2;

WITH CTE_Processos AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY ClienteID ORDER BY ID DESC) AS Ordem,
        ClienteID,
        EP
    FROM estagioprocesso
)

SELECT ClienteID FROM CTE_Processos WHERE Ordem = 1 AND EP = @ep;

In this case, do the following:

var clientes = db.EtapaProcess
    .GroupBy(etapa => etapa.ClienteID)
    .Where(grupo => grupo
        .OrderByDescending(etapa => etapa.ID)
        .Select(etapa => etapa.EP)
        .FirstOfDefault() == id)
    .Select(grupo => grupo.Key);