LINQ with two groupings

Question

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]

sohail.hussain.dyn · Accepted Answer · 2013-01-13 17:30:31Z

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

Sergey Berezovskiy · Accepted Answer · 2013-01-13 17:44:56Z

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

k.m · Accepted Answer · 2013-01-13 17:50:40Z

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

Collectives™ on Stack Overflow

LINQ with two groupings

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related