Lambda, Where with subquery

Question

Lambda, Where with subquery

Navigation

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

2

I need a query like this in 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 StepProcess table which is the last EP of that customer (order by ID DESC) I get only 1 (top 1) and check this EP if it is equal to the number I want to return.

I want to bring all the steps of the clients that the most recent step has the EP = id (2).

I tried:

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 does not work. Ex: There are 188 records that have already passed through EP = 2, but with EP = 2 only 5 records and with the above query it returns 0

c# asp.net-mvc

asked by anonymous 03.02.2016 / 18:24

2 answers

3

Your query is quite wrong. From what I understand from it, 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);

03.02.2016 / 19:21

How to traverse a SELECT rows in a STORED PROCEDURE in SQL Server? Change content by clicking icon and change icon

score 2 · Accepted Answer

By what I understand, you want to bring all the steps of the clients that the most recent step has the EP = id (2).

In this case, you will have to group by customer, 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 just 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);

UPDATE

I believe you do not need to make a connection with 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);