LINQ query with sum

Question

LINQ query with sum

Navigation

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

7

I have the following query in LINQ

var dados = _db.Contratos
    .Where(a => a.Adesoes.Any(b => b.Pago))
    .Select(a => new
    {
        Contrato = a.Numero,
        ValorTASenior = a.Adesoes.Where(b => b.Pago).Sum(b => b.ValorTASenior),
        ValorTAMaster = a.Adesoes.Where(b => b.Pago).Sum(b => b.ValorTAMaster),
        ValorTAConsultor = a.Adesoes.Where(b => b.Pago).Sum(b => b.ValorTAConsultor),
        ValorCliente = a.Adesoes.Where(b => b.Pago).Sum(b => b.ValorCliente)
    });

I would like to know how I can simplify the sum of the values and avoid the a.Adesoes.Where(b => b.Pago) for each sum that I need to do.

Entities & Context

public class MeuContext : DbContext
{
    public DbSet<Contrato> Contratos { get; set; }
}

public class Contrato
{
    [Key]
    public int ContratoId { get; set; }

    public int Numero { get; set; }
    public virtual ICollection<Adesao> Adesoes { get; set; }
}

public class Adesao
{
    [Key]
    public int AdesaoId { get; set; }
    public int ContratoId { get; set; }
    public bool Pago { get; set; }
    public int ValorTASenior { get; set; }
    public int ValorTAMaster { get; set; }
    public int ValorTAConsultor { get; set; }
    public int ValorCliente { get; set; }
    public virtual Contrato Contrato { get; set; }
}

c# linq entity-framework-6

asked by anonymous 14.12.2017 / 17:19

2 answers

1

Another possibility is to start the query by the Adhesion entity already filtered by the paid, together with a group by .

var dados = _db.Adesoes
    .Where(b => b.Pago)
    .GroupBy(a => a.Contrato)
    .Select(a => new
    {
        Contrato = a.Key.Numero,
        ValorTASenior = a.Sum(b => b.ValorTASenior),
        ValorTAMaster = a.Sum(b => b.ValorTAMaster),
        ValorTAConsultor = a.Sum(b => b.ValorTAConsultor),
        ValorCliente = a.Sum(b => b.ValorCliente)
    });

The answer from @MarconcilioSouza generates a smaller SQL, however the time shown MiniProfiler is a little bigger than using Group By .

Using Group By I have as run time around 150ms and using as the response of @MarconcilioSouza, I have around 200ms.

15.12.2017 / 14:19

What does bank return INSERT 0 1 mean? How to clear a 301 redirect that has already been stored in the browser cache?

score 5 · Accepted Answer

You can create a select with your filter before the final select. As you commented I will not change your where, but the solution would be like this.

var dados = _db.Contratos
    .Where(a => a.Adesoes.Any(b => b.Pago))

    .Select(a => new
    {
        Contrato = a.Numero,
        Adesoes = a.Adesoes.Where(b => b.Pago),
    })
    .Select(a => new
    {
        Contrato = a.Contrato,
        ValorTASenior = a.Adesoes.Sum(b => b.ValorTASenior),
        ValorTAMaster = a.Adesoes.Sum(b => b.ValorTAMaster),
        ValorTAConsultor = a.Adesoes.Sum(b => b.ValorTAConsultor),
        ValorCliente = a.Adesoes.Sum(b => b.ValorCliente)
    });