How to concatenate sublists from a list and add values to them using Linq with C #

Navigation
1

Hello, I have the following 2 classes: 

public class EstoqueItem {
    [Key]
    public int Id { get; set; }
    public decimal Qtd { get; set; }
    public Estoque Estoque { get; set;}
}

public class Estoque {
    [Key]
    public int Id { get; set; }
    public ICollection<EstoqueItem> Itens { get; set; }
}

At some point, I return a List<Estoque> and need to group all ICollection<EstoqueItem> by Id and add their quantities. It would look something like the SQL below: 

SELECT Id, SUM(Qtd) as Qtd,
FROM EstoqueItem
WHERE /* as condições que retornam a lista aqui */
GROUP BY Id

How do I concatenate the sublists of the stock, grouping by id and summing their quantities in the end?

    
asked by anonymous 14.09.2018 / 15:30

3 answers

1

You can use SelectMany

Example: 

estoques.SelectMany(x => x.Itens)
        .GroupBy( i=> i.Id)
        .Select(g => new { Id = g.Key, Total = g.Sum(x => x.Qtd) });
    
14.09.2018 / 16:35
1

You need to use the SelectMany method to do what you call "concatenate the lists" and then do the grouping by the item's ID and then add up the quantities.

So 

var gp = estoques.SelectMany(x => x.Itens)
                 .GroupBy(x => x.Id)
                 .Select(x => new { Id = x.Key, Qtd = x.Sum(y => y.Qtd) });

See working in .NET Fiddle

  • SelectMany "flatten" the result for a list of EstoqueItem

  • GroupBy will be applied to this list of EstoqueItem and will return a collection where each item will have a key, this being the value of the property defined as grouper ( Id , in example) and you will have a collection of items related to this key. From this collection of items (list of EstoqueItem , in this case) you can apply any valid operation with collections
  • Select causes each result item to be mapped to a new object where the Id property refers to the key of each item in the collation and the Qtd property refers to the result of the operation Sum(x => x.Qtd) (add the Qtd property for each item in the collection) above the collation value
14.09.2018 / 18:30
0

You can use a Sum in the selection to add up the quantities in each stock 

// estoques => representa a sua coleção de estoques ou a entidade do EF

foreach (var e in estoques.Select(x => new { Key = x.Id, Sum = x.Itens.Sum(y => y.Qtd) }))
{
    Console.WriteLine(string.Format("Estoque: {0}, Total: {1}", e.Key, e.Sum));
}

Example on DotNetFiddle

    
14.09.2018 / 17:07
Convert .FRM to .SQL Date in SQL ORACLE