Subquery in Lambda

Question

Subquery in Lambda

Navigation

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

1

I would like to do a subquery, today in 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 make a select in the table clients where in the table estagioprocesso the last int_EP is = 4 (or the variable you want)

The query below also returns similar (I did not query 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 query:

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

Thatis,itshouldonlybringtherecordint_ID=8718,asint_IDC(IdClient)ithas3recordsbutthelastEPisin4.

Whatthequeryshoulddo:Returnthelaststage_processoftheclient,ietheclientcanhaveseveral,butwhatisthelast(TOP1...orderbyIDDESC)

Inmyqueryitreturnsall3records. WithId=4,okitfinds,howeveritdoesall(3)recordsshouldonlybringtheint_ID=8718

WithId=2itdoesnotfindcorrectcorrectresult:)

Sorryforthebigquestion,butwantedtoshowthewholeprocess.Thisquestionisalreadyanupdateofmyinitialquestion: Lambda, Where with subquery

sql-server c# asp.net-mvc

asked by anonymous 17.02.2016 / 14:42

2 answers

1

You can do this:

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

23.02.2016 / 14:00

Get cursor position with jquery Sql server - default

score 1 · Accepted Answer

Select the last (highest) step for each client:

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. Since I did not have the schema for the tbl_Admin entity, I kicked the field names of this table.