How do I create a nested group-by dictionary using LINQ?

Question

How would I create a nested grouping for the table below, using LINQ? I want to group by Code, then by Mktcode.

Code Mktcode Id ==== ======= ==== 1    10      0001  2    20      0010  1    10      0012  1    20      0010  1    20      0014  2    20      0001 2    30      0002 1    30      0002 1    30      0005 

I'd like a dictionary, in the end, like

Dictionary<Code, List<Dictionary<Mktcode, List<Id>>>> 

So the values of this dictionary would be

{1, ({10,(0001,0012)}, {20,(0010,0014)}, {30, (0002, 0005)})}, {2, ({20,(0001, 0010)}, {30, (0020)} )} 

Answer 1

I'd think of it this way:

• You're primarily grouping by code, so do that first
• For each group, you've still got a list of results - so apply another grouping there.

Something like:

var groupedByCode = source.GroupBy(x => x.Code);  var groupedByCodeAndThenId = groupedByCode.Select(group =>     new { Key=group.Key, NestedGroup = group.ToLookup                                          (result => result.MktCode, result => result.Id));  var dictionary = groupedByCodeAndThenId.ToDictionary     (result => result.Key, result => result.NestedGroup); 

That will give you a Dictionary<Code, Lookup<MktCode, Id>> - I think that's what you want. It's completely untested though.

Answer 2

You can build lookups (Kinds of Dictionary<,List<>>) using group by into

var lines = new [] {     new {Code = 1, MktCode = 10, Id = 1},     new {Code = 2, MktCode = 20, Id = 10},     new {Code = 1, MktCode = 10, Id = 12},     new {Code = 1, MktCode = 20, Id = 10},     new {Code = 1, MktCode = 20, Id = 14},     new {Code = 2, MktCode = 20, Id = 1},     new {Code = 2, MktCode = 30, Id = 2},     new {Code = 1, MktCode = 30, Id = 2},     new {Code = 1, MktCode = 30, Id = 5}, };  var groups = from line in lines     group line by line.Code     into codeGroup     select new     {         Code = codeGroup.Key,         Items = from l in codeGroup             group l by l.MktCode into mktCodeGroup             select new             {                 MktCode = mktCodeGroup.Key,                  Ids = from mktLine in mktCodeGroup                     select mktLine.Id             }     };