CODEWITHSUNDEEP
c#asp.netlinq
Isma Haro
Isma Haro

Reputation: 263

Group by with select new array using linq

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

Upvotes: 0

Views: 3611

Answers (2)

ocuenca
ocuenca

Reputation: 39386

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

Upvotes: 1

tede24
tede24

Reputation: 2354

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.

Upvotes: 1

Related Questions