Subconsulta in Lambda

Question

I would like to do a sub-query today on SQL:

SELECT        intid as IDCliente,
                             (SELECT        TOP (1) int_EP
                               FROM            tbl_estagioprocesso
                               WHERE        (int_IDC = tbl_Admin.intid)
                               ORDER BY int_ID DESC) AS int_EP
FROM            tbl_Admin WITH (nolock)
WHERE        ((SELECT        TOP (1) int_EP
                            FROM            tbl_estagioprocesso AS tbl_estagioprocesso_1
                            WHERE        (int_IDC = tbl_Admin.intid)
                            ORDER BY int_ID DESC) = 4)

In case I do a select in the clients table where in the stage table the last int_EP is=4 (or the variable you want)

The query below also returns similar (not done with query in the client table)

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

WITH CTE_Processos AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY [int_IDC] ORDER BY [int_ID] DESC) AS Ordem,
        [int_IDC],
        [int_EP]
    FROM tbl_estagioprocesso
)

SELECT [int_IDC] FROM CTE_Processos WHERE Ordem = 1 AND [int_EP] = @ep;

Now how to do in Lambda or Linq?

My Model:

[Table("tbl_estagioprocesso")]
public class EstagioProcesso
{
    [Key]
    [Column("int_ID")]
    public int EpId { get; set; }

    [Column("int_IDC")]
    public int ClienteId { get; set; }

    [Column("int_EP")]
    public byte EP { get; set; }

    [Column("sdt_Data")]
    public DateTime Data { get; set; }

    [Column("int_IDFuncionario")]
    public int IdFuncionario { get; set; }

    [ForeignKey("ClienteId")]
    public virtual Cliente Cliente { get; set; }

How I tried the consultation:

var epClientes = db.EstagioProcess
                .Include(etapa => etapa.Cliente)
                .GroupBy(etapa => etapa.ClienteId)
                .Where(grupo => grupo
                    .OrderByDescending(etapa => etapa.EpId).Take(1)
                    .Select(etapa => etapa.EP)
                    .FirstOrDefault() == id);

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

Data from my SQL

Where:

int_ID (autonumeravel)
int_IDC (ClienteId)
int_EP (Id do Estagio do Processo)

That is, it should only bring the record int_ID=8718, because the int_IDC (Idclient) it has 3 records but the last EP is in 4.

What the consultation should do: Return the last client process step, that is, the client may have several, but which one is the last (TOP 1 ...order by ID DESC)

In my query he returns the 3 records. With Id=4, ok it finds, but brings all records (3) should only bring the int_ID=8718

With Id=2 it does not find result, ok correct :)

Sorry the question got big, but I wanted to show you the whole process. This question is already an update of my initial question: Lambda, Where com subconsulta

Answer 1

you can do so:

var processos = 
    from processo in db.EstagioProcess
    group new { 
        ClienteId = processo.ClienteId, 
        EpId = processo.EpId, 
        EP = processo.EP 
    } by processo.ClienteId into grupo
    select grupo.OrderByDescending(processo => processo.EpId).FirstOrDefault();

var clientes = 
    from processo in processos
    where processo.EP == 4
    select processo.ClienteId

Answer 2

Select the last (largest) step for each customer:

var query = from regAdmin in db.tbl_Admin
            select new {
                  Cliente = regAdmin,
                  EtapaAtual = (from regEtapa in db.tbl_estagioprocesso
                                where regEtapa.ClienteId == regAdmin.Id
                                orderby regEtapa.EP descending
                                select regEtapa.EP).FirstOrDefault()
            }

Select only clients that are in a specific step (from the variable containing the query created above):

var clientesNaEtapaQuatro = (from clienteEtapa in query
                             where clienteEtapa.EtapaAtual == 4
                             select clienteEtapa.Cliente).ToList()

Note: The above queries are not equivalent to the SQL you posted, but should give you a north. As you did not have the schema for the entity tbl_Admin, I kicked the name of the fields in this table.