c#/linq data grouping

Question

I have a Product class something like:

class Product { public string Name; public int Year; public int Vendor; public int Count; }

Data input like

Name Year Vendor Count 'A' 1990 1 15 'A' 1990 2 20 'A' 1998 4 5 'B' 1995 1 15 'B' 1995 2 2

I want to transform the data into a new structure like

class NewStructure { public string Name; public int Year; Dictionary<int, int> Vendor_Count; }

The Dictionary field is used to store {Vendor, Count} pair, the pair of Name and Year would have a correspodning dictionary. The result would be

Name Year Vendor Count 'A' 1990 1 15 2 20 'A' 1998 4 5 'B' 1995 1 15 2 2

I tried the following linq query

from input in inputList group inputList by new { input.Name, input.Year } into grouping orderby grouping.Key.Name, grouping.Key.Year select new { ProductName = grouping.Key.Name, OriginYear = grouping.Key.Year, Vendor_Count = grouping. }

But I can't get the 'Vendor_Count' right. I wonder what should I do?

Daniel Hilgarth · Accepted Answer · 2012-02-03 11:24:53Z

Use this if the combination of (Name, Year, Vendor) is unique:

inputList.GroupBy(x => new { x.Name, x.Year }) .Select(x => new { ProductName = x.Key.Name, OriginYear = x.Key.Year, Vendor_Count = x.ToDictionary(y => y.Vendor y => y.Count) }) .OrderBy(x => x.ProductName).ThenBy(x => x.OriginYear);

Use this if it isn't unique:

inputList.GroupBy(x => new { x.Name, x.Year }) .Select(x => new { ProductName = x.Key.Name, OriginYear = x.Key.Year, Vendor_Count = x.GroupBy(y => y.Vendor) .ToDictionary(y => y.Key, y => y.Sum(z => z.Count)) }) .OrderBy(x => x.ProductName).ThenBy(x => x.OriginYear);

would x be List<Product> rather than Product. It seems to be a list and I got error.
This time I got 'An item with the same key has already been added.'
@esun203: Please see update. Now it works correctly, even when the combination of (Name, Year, Vendor) is not unique. I was totally confused about the query syntax because I never use it.

Aidan · Accepted Answer · 2012-02-03 11:07:27Z

Something like this :

 var p1 = new Product(){Count=15, Name="A", Vendor = 1, Year = 1990}; var p2 = new Product() { Count = 20, Name = "A", Vendor = 2, Year = 1990 }; var p3 = new Product() { Count = 5, Name = "A", Vendor = 4, Year = 1998 }; var p4 = new Product() { Count = 15, Name = "B", Vendor = 1, Year = 1995 }; var p5 = new Product() { Count = 2, Name = "B", Vendor = 1, Year = 1995 }; var inputList = new[] {p1, p2, p3, p4, p5}; var newStructure = inputList .GroupBy( p => new { p.Name, p.Year } ) .Select( g => new { g.Key.Name, g.Key.Year, VendorCount = g.ToDictionary(p => p.Vendor, p => p.Count) } );

Although I don't think you can use Vendor as a dictionary key, because they are not unique as it stands

In the data in the original question Name/Vendor/Year form a unique set. Your set B has two Vendor ones for 95. This may be possible in the data but it isn't in the original spec... Thus this seems to work perfectly. :)

Collectives™ on Stack Overflow

c#/linq data grouping

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related