How to create pivot table with dynamic column using linq tree expression

Question

I am writing an asp.net web application C#; I have a datatable in memory named " table1" with three columns " country", " productId" and " productQuantity"; I want to expand this table to get a new table (suppose " table2") having the first column " country" as a fixed column and the dynamic number and column names " product_1", " product_2', ...,' product_n'according to the total number of works, existing in ' table1'; the first column " country" should contain the name of the country; dynamically created columns product_1', ' product_2', ..., ' product_n' should contain productQuantity,which was sold for each specific product in the specified country

I use Linq query expressions to write code; the problem is that I cannot hard code the names with the values ​​of the products; I cannot predict how many products exist in a datatable; while 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 will give an example of what " table1" looks like and how " table2" should look like this:

see an example of the image of two tables table1 and table2

can someone help me find a solution to create a pivot table with a dynamic column using the linq tree expression or other linq methods; Any help would be greatly appreciated.

Answer 1

sql. linq, sql.

, , .

{country, product} sql ( , .Select(x => x.product).Distinct() .., datagrid).

Answer 2

.

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

.