0

I need help with a linq query

I have a table of Products with names, inventory and other fields. There can be "repeated" product names but with "different inventory".

I need 3 things:

  1. Select all products and group them by name
  2. In the SELECT, SUM the inventory field of the products that have the same name
  3. The Select should be retrieved on an "array" because I will send it through JSON

So for example if I have a product pen with inventory 1.

And then another product with same name "pen" but with inventory 3

Then my select have to give me just 1 row with name "pen" and inventory "4"

I used to have a query like this:

        var result = (from c in Products
                      where c.Inventory != 0
                      select new[] {
                         Convert.ToString(i++),
                         c.Name,
                         c.Family,
                         c.Inventory});

It works but now I need to add the "group by" product name and also sum the inventory of each product with the same name but I don't know how

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Isma Haro
  • 263
  • 6
  • 16
  • This has been answered here: [link](http://stackoverflow.com/questions/3414080/using-groupby-count-and-sum-in-linq-lambda-expressions) –  Feb 21 '16 at 20:41

2 Answers2

1

I'll write in method based sintax as not used to query based:

var result = products.GroupBy(p=>new {p.Name,p.Family},(key,g)=>new {
    key.Name,
    key.Family,
    Inventory=g.Sum(x=>x.Inventory)
}).ToArray();

Note I include both Name and Family in grouping key as your result needs both.

tede24
  • 2,304
  • 11
  • 14
  • Oh okay! And what about if I need more fields on the select, for example: String.Format("{0:c}", c.Price) – Isma Haro Feb 21 '16 at 21:00
  • Just add `Price = String.Format("{0:c}", g.Sum(x => x.Price))` or any other aggregation function in the `Select` statement . – Arthur Rey Feb 21 '16 at 21:06
  • You can add it as key (if it's the same for all products with same name) or as a second calculated field as Price=g.Average(x=>x.Price) or any other aggregation function, depending on your needs – tede24 Feb 21 '16 at 21:07
  • Say for example I have a field "Country" which is a String but this can't be grouped because there are products with the same name that comes from different countries. In this I case , I think I can't aggregate a "string" and also I cant group since there are different countries. I don't matter in this case which country it should show, what could be the solution here? – Isma Haro Feb 21 '16 at 21:17
  • If you can show any country in the product group, so you can do this: AnyCountry = g.Select(x=>c.Country).FirstOrDefault(). – tede24 Feb 21 '16 at 21:20
  • I think there is no need to project first all the countries in a group to select later the first one.You can select the first element in the group and get the value of property you need. – ocuenca Feb 21 '16 at 21:39
  • @octavioccl how would it be? – tede24 Feb 21 '16 at 21:41
1

If I understand well your problem you are looking something like this:

  var result = (from c in Products
                where c.Inventory != 0
                group c by new{c.Name,c.Family} into g
                select new {g.Key.Name, g.Key.Family, Inventory=g.Sum(e=>e.Inventory)}).ToArray();

You can find more info about how to use group clause in this msdn page.

Update

If you want to add another field to your projection which you don't mind which value have (like you describe in your comment) you can use First extension method:

new {g.Key.Name, 
     g.Key.Family,
     Country=g.First().Country, 
     Inventory=g.Sum(e=>e.Inventory)}
ocuenca
  • 38,548
  • 11
  • 89
  • 102