LINQ query with sum

Asked

Viewed 195 times

7

I have the following appointment at 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 summations of values and avoid the a.Adesoes.Where(b => b.Pago) for every sum I need to make.

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

  • you are not already doing this in your first Where ? . Where(a => a.Adesoes.Any(b => b.Paid))

  • Yes, but I’d like to avoid repeating that where for every sum.

  • I think I got the password. your problem is in your any ... your Where is satisfied with the first Paid Membership = true... you need your select to receive all Paid Memberships so you do not need the filter within select.

  • My Any guarantees that I only return contracts that have subscriptions in paid condition, but even then some contracts may have subscriptions paid and unpaid. So that’s why I do Where no select to add up only paid memberships.

2 answers

5


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)
    });

  • All right, it was something I thought I’d do. Thank you

  • Can you tell if this is efficient, or triggers SQL performance?

  • @Maniero I have this doubt too. I will see how the performance of this there.

  • @Maniero, while Asqueryable is the query being mounted, as it mounts, it will demand how the model is created. depending on whether it can create a subselect or make a Join returned only the fields that are in select.

  • @Marconciliosouza is exactly what I question, something tells me that the SQL generated with this must be very bad. When Pablo commented, I thought about taking the collection to filter, I would test before because I think it would look really bad, but then you answered and I gave up.

  • @Maniero, the way this performance will be much better than the way he mounted the question, practically he is creating a subselect for each select field that has a SUM. one way around this would be to use groupby, but you would need to know the context of what would be returned in the query to get an idea of what to do better.

  • @Marconciliosouza I thought about GroupBy or even at some Join. But I hate all these things, I’m no longer a fan of SQL, an abstraction on top of it is even worse.

  • 1

    SQL generated, doing group by, was 178 lines and SQL using the form of the @Marconciliosouza response was 105 lines. using the answer solution the running time shown by Miniprofile is 204ms, using with group by is around 150ms.

  • is not always the generated sql is a beautiful thing to be see, It helps a lot in building a fast application. but if you really need performance a good O/RM is the Dapper. Depending on your application you can combine the two o/rm and use the Dapper where you need a higher performance. But if you are going to use the EF in a way that is easier and gives an acceptable performance for your application. as the group by you have earnings and behind you will not even see the code so do not have to worry about it let the EF manage with it

  • 1

    I will add as response the option with Group By

  • Cool. It’s good to have more reference of how to do.

Show 6 more comments

1

Another possibility is to start the consultation by the Accession entity already filtered for paid, along 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 @Marconciliosouza response generates a smaller SQL, however the time shown Mini-profiler is a little bigger than using Group By.

Using the Group By I have running time around 150ms and using as per the reply of @Marconciliosouza, I have around 200ms.

  • You have not tried to make group by just by property Number ?

  • I’ll make that change and see how it looks

Browser other questions tagged c# linq entity-framework-6

You are not signed in. Login or sign up in order to post.