LINQ with two groups

Question

I am struggling with double grouping using C # / LINQ for data similar in form to the following example. I start with a list of TickItems coming from the data layer, and now I have it configured 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 final result. In the end, it should have this form:

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

I was able to execute each of the groups separately (a group by ItemName and a group by categories), but I was not able to execute both groups in the same LINQ statement. My code is:

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

Can anyone help?

[edit: I can use any method or query syntax, depending on which it is easier to visualize the process with

Answer 1

 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 returns a sequence of anonymous objects representing items grouped by category. Each category object will have a list of anonymous objects that will contain the totals for each element name.

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

Answer 2

Please see this post.

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

Multiple groupings can be performed using the new keyword.

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

Answer 3

GroupBy has an overload that allows you to specify the conversion of the result, 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) }) });