CODEWITHSUNDEEP
c#linqgroup-by
Jim Marquardt
Jim Marquardt

Reputation: 4482

LINQ with two groupings

I am struggling with a double grouping using C#/LINQ on data similar in shape to the following example. I'm starting with a list of TickItems coming from the data layer, and I have now got it shaped as such:

TickItem { ItemName: string; QtyNeeded: int; QtyFulfilled: int; Category: string; } 

List<TickItem> items = new List<TickItem>();
items.Add("apple", 1, 0, "food");
items.Add("orange", 1, 1, "food");
items.Add("orange", 1, 0, "food");
items.Add("bicycle", 1, 1, "vehicle");
items.Add("apple", 1, 1, "food");
items.Add("apple", 1, 0, "food");
items.Add("car", 1, 1, "vehicle");
items.Add("truck", 1, 0, "vehicle");
items.Add("banana", 1, 0, "food");

I need to group this data by Category, with the sum of each numeric column in the end result. In the end, it should be shaped like this:

{ "food":    { "apple"  : 3, 1 },
             { "banana" : 1, 0 },
             { "orange" : 2, 1 } },
{ "vehicle": { "bicycle": 1, 1 },
             { "car"    : 1, 1 },
             { "truck"  : 1, 0} }

I have been able to do each of the groupings individually (group by ItemName and group by Category), but I have not been able to perform both groupings in a single LINQ statement. My code so far:

var groupListItemName = things.GroupBy(tiλ => tiλ.ItemName).ToList();
var groupListCategory = things.GroupBy(tiλ => tiλ.Category).ToList();

Can anyone help?

[edit: I can use either method or query syntax, whichever is easier to visualize the process with]

Upvotes: 4

Views: 277

Answers (3)

k.m
k.m

Reputation: 31444

GroupBy has an overload that lets you specify result transformation, for example:

var result = items.GroupBy(i => i.Category,
    (category, categoryElements) => new
    {   
        Category = category,
        Elements = categoryElements.GroupBy(i => i.ItemName,
            (item, itemElements) => new 
            {
                Item = item,
                QtyNeeded = itemElements.Sum(i => i.QtyNeeded),
                QtyFulfilled = itemElements.Sum(i => i.QtyFulfilled)
            })
    });

Upvotes: 0

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

var query = from i in items
            group i by i.Category into categoryGroup
            select new
            {
               Category = categoryGroup.Key,
               Items = categoryGroup.GroupBy(g => g.ItemName)
                                    .Select(g => new
                                    {
                                        ItemName = g.Key,
                                        QtyNeeded = g.Sum(x => x.QtyNeeded),
                                        QtyFulfilled = g.Sum(x => x.QtyFulfilled)
                                    }).ToList()
            };

This query will return sequence of anonymous objects representing items grouped by category. Each category object will have list of anonymous objects, which will contain totals for each item name.

foreach(var group in query)
{
    // group.Category
    foreach(var item in group.Items)
    {
        // item.ItemName
        // item.QtyNeeded
        // item.QtyFulfilled 
    }
}

Upvotes: 1

sohail.hussain.dyn
sohail.hussain.dyn

Reputation: 1531

Please have a look at this post.

http://sohailnedian.blogspot.com/2012/12/linq-groupby-with-aggregate-functions.html

Multiple grouping can be done via new keyword. 

empList.GroupBy(_ => new { _.DeptId, _.Position })  
    .Select(_ => new  
    {  
        MaximumSalary = _.Max(deptPositionGroup => deptPositionGroup.Salary),  
        DepartmentId = _.Key.DeptId,  
        Position = _.Key.Position  
    }).ToList()

Upvotes: 2

Related Questions