how to create a pivot table with dynamic column using linq tree expression

Question

I'm writing an asp.net C# web application; i have an in-memory datatable named 'table1' having three columns 'country', 'productId' and 'productQuantity'; i want to pivot that table in order to obtain a new table (suppose 'table2') having the first column 'country' as a fixed column and a dynamic number and names of columns 'product_1', 'product_2', ..., 'product_n' according to the total number of products existing in 'table1'; the first column 'country' must contain the country name; the dynamic generated columns 'product_1', 'product_2', ..., 'product_n' must contain the productQuantity that has been selled for each specific product in the specified country

I'm using Linq query expressions to write the code; the problem is that i cannot hard-code the names neither the values of the products; i cannot predict how much products exist in the datatable; for now, I'm testing the results using the following expression :

var query = table1.AsEnumerable()
                .GroupBy(c => c.country)
                .Select(g => new
                {
                    country = g.Key,
                    product_1 = g.Where(c => c.productId == "a30-m").Select(c => c.productQuantity).FirstOrDefault(),
                    product_2 = g.Where(c => c.productId == "q29-s").Select(c => c.productQuantity).FirstOrDefault(),
          .
          .
          .
                    product_n = g.Where(c => c.productId == "g33-r").Select(c => c.productQuantity).FirstOrDefault()
                });

i'm giving an example on how 'table1' looks like and how 'table2' must look like :

view example image of the two tables table1 and table2

can anyone please help me finding a solution for creating a pivot table with dynamic column using linq tree expression or another linq methods; any help would be much appreciated.

Answer 1

I have been using this extension for pivoting list.

 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;
    }

Hope this help.

Answer 2

It is not possible to query a dynamic number of columns in sql. By extension it is not possible to do that with linq either as it is based on sql.

So you are out of luck, sorry.

However you can request all existing pairs of {country, product} from sql server and do the remaining processing on the client (reconstruct countries and products by doing .Select(x => x.product).Distinct() and so on, and then dynamically creating columns in a datagrid).