LINQ -- how to pivot data usefull for charting

Extend Dictionary for var to have access to the extention function

 

using System;

using System.Collections.Generic;

using System.Linq;

 

namespace ReflectionIT.Training {

 

    public static class LinqExtenions {

 

        public static Dictionary<TFirstKey, Dictionary<TSecondKey, TValue>> Pivot<TSource, TFirstKey, TSecondKey, TValue>(this IEnumerable<TSource> source, Func<TSource, TFirstKey> firstKeySelector, Func<TSource, TSecondKey> secondKeySelector, Func<IEnumerable<TSource>, TValue> aggregate) {

            var retVal = new Dictionary<TFirstKey, Dictionary<TSecondKey, TValue>>();

 

            var l = source.ToLookup(firstKeySelector);

            foreach (var item in l) {

                var dict = new Dictionary<TSecondKey, TValue>();

                retVal.Add(item.Key, dict);

                var subdict = item.ToLookup(secondKeySelector);

                foreach (var subitem in subdict) {

                    dict.Add(subitem.Key, aggregate(subitem));

                }

            }

 

            return retVal;

        }

 

    }

}

 

Use any ‘var’ typed variable [L in this case] with your resultset

 

var result1 = L.Pivot(emp => emp.Department, emp2 => emp2.Function, lst => lst.Sum(emp => emp.Salary));

 

        foreach (var row in result1) {

            Console.WriteLine(row.Key);

            foreach (var column in row.Value) {

                Console.WriteLine("  " + column.Key + "\t" + column.Value);

 

            }

        }

 

        Console.WriteLine("----");

 

        var result2 = l.Pivot(emp => emp.Function, emp2 => emp2.Department, lst => lst.Count());

 

        foreach (var row in result2) {

            Console.WriteLine(row.Key);

            foreach (var column in row.Value) {

                Console.WriteLine("  " + column.Key + "\t" + column.Value);

 

            }

        }

 

Reference : http://www.extensionmethod.net/Details.aspx?ID=147#