How to group by 2 properties and sum the values

Asked

Viewed 1,774 times

3

I have no idea how to do this consultation on LINQ. How can I from sales list. Separate the sales per hour, then group the products and add up the quantities sold?

 public class Venda
 {
   public int Id {get;set;}
   public DateTime DataHora {get;set;}
   public List<ProdutoVendido> ProdutosVendidos {get;set;}
 }

 public class ProdutoVendido
 {
   public Produto Produto {get;set;}
   public int Quantidade {get;set;}
 }

 public class Produto
 {
    public int Id {get;set;}
    public string Nome {get;set;}
 }

In SQL would look like this:

select hour(dthr_venda), nm_produto, sum(cd_qt) from produto_e_venda
join venda using(cd_venda)
join produto using (cd_produto)
group by cd_produto, hour(dthr_venda)
order by 1;
c# linq winforms

  • product_e_sale is the Product class?

  • Productovendido refers to product table_e_sale yes (:

  • The model looks wrong, and you’re using Entity ?

  • But Productovendido has no Id?

  • I simplified, there are other properties.

  • Not because this table has two primary keys, being sales code and product code. I’m also using Effluent nhibernate to make the classes

  • Enter the codes completely if it is not difficult to answer :)

Show 2 more comments

1 answer

2


The query to group each product per hour and calculate the quantities sold will be:

var vendasPorProduto = vendas.GroupBy(v => v.DataHora.Hour)
                                .Select(g => new
                                    {
                                        Hora = g.Key,
                                        ProdutosDetalhados = g.SelectMany(v => v.ProdutosVendidos)
                                                            .GroupBy(pv => pv.Produto)
                                                            .Select(v => new
                                                                {
                                                                    Produto = v.Key,
                                                                    Quantidade = v.Sum(pv => pv.Quantidade)
                                                                })
                                    });

Extra:

If you want to calculate the total number of products sold in one hour (was the secondary product of answering your question):

var todosProdutosPorHora = vendas.GroupBy(v => v.DataHora.Hour)
                                    .Select(g => new
                                        {
                                            Hora = g.Key,
                                            Total = g.Sum(venda =>
                                                        venda.ProdutosVendidos.Sum(pv => pv.Quantidade))
                                        });

Browser other questions tagged c# linq winforms

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