Wednesday, July 23, 2014

C# LINQ - Grouping Into Dictionary

Imagine a data table such as this.

ID
Number
Date
Type
Location
1 987654321 12-07-2014 Warehouse China
2 987654321 23-06-2014 Warehouse China
3 987654322 3-06-2014 Storage USA
4 987654321 24-05-2014 Storage UK
5 987654321 13-04-2014 Warehouse China
6 987654322 23-07-2014 Boxed USA
7 987654321 27-02-2014 Boxed China
8 987654323 5-07-2014 Warehouse UK

In this table, there are storage types and each type has a number associated with them.

Once I get this from the DB to a list of objects, I need to put them into a structure something like this.

Type ( Number )
ID
Date
Location
1 23-07-2014 China



In simple JSON like structure, data would look like following.

Items : [
          {
            Type,
            Number,
            Data : [
                     { ID, Date, Location },
                     { ID, Date, Location }
                             ....
                   ]
          },
          {
            Type,
            Number,
            Data : [
                     { ID, Date, Location },
                     { ID, Date, Location }
                             ....
                   ]
          },
               ....
       ]

Basically, I have an array of items,
in each item we have the Type and Number with a list of other details.

Assume I want these data to be put into a report. then the report structure would be something like:

How can I put this into the above structure?

First, group them by the TYPE.
Then Add the rest to a dictionary where key is the TYPE.
For the value of each dictionary item, Group them by NUMBER and add to another dictionary.

IEnumerable<StorageItem> dbItems = GetFromDB();

                var storageGroupedData = dbItems
                    .GroupBy(t => t.Type)
                    .ToDictionary(t => t.Key, t => t.GroupBy(k => k.Number)
                        .ToDictionary(k => k.Key, k => k.ToList()));

                var result = from kType in storageGroupedData
                             from kNumber in kType.Value
                             select new Item
                             {
                                 Number = kNumber.Key,
                                 Type = kType.Key, 
                                 Data = kNumber.Value.Select(t => new LineItem()
                                 {
                                     ID = t.ID ,
                                     Date = t.Date ,
                                     Location= t.Location
                                 }).ToList()
                             };

var structuredItems = result.ToList();