Month: September 2023

My Previous post explains how to convert a column to row in JavaScript array. In this post, we will do the same thing but with C# Array and DataTable using the power of LINQ or Lambda expression. For simplicity, I am using the same data.

C# Array To Pivot DataTable:

Here is the C# array object:

var data = new[] { 
              new { Product = "Product 1", Year = 2009, Sales = 1212 },
              new { Product = "Product 2", Year = 2009, Sales = 522 },
              new { Product = "Product 1", Year = 2010, Sales = 1337 },
              new { Product = "Product 2", Year = 2011, Sales = 711 },
              new { Product = "Product 2", Year = 2012, Sales = 2245 },
              new { Product = "Product 3", Year = 2012, Sales = 1000 }
          };

On Googling, I found the following generic method in StackOverflow thread.

public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
    this IEnumerable<T> source,
    Func<T, TColumn> columnSelector,
    Expression<Func<T, TRow>> rowSelector,
    Func<IEnumerable<T>, TData> dataSelector)
        {
            DataTable table = new DataTable();
            var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
            table.Columns.Add(new DataColumn(rowName));
            var columns = source.Select(columnSelector).Distinct();
 
            foreach (var column in columns)
                table.Columns.Add(new DataColumn(column.ToString()));
 
            var rows = source.GroupBy(rowSelector.Compile())
                             .Select(rowGroup => new
                             {
                                 Key = rowGroup.Key,
                                 Values = columns.GroupJoin(
                                     rowGroup,
                                     c => c,
                                     r => columnSelector(r),
                                     (c, columnGroup) => dataSelector(columnGroup))
                             });
 
            foreach (var row in rows)
            {
                var dataRow = table.NewRow();
                var items = row.Values.Cast<object>().ToList();
                items.Insert(0, row.Key);
                dataRow.ItemArray = items.ToArray();
                table.Rows.Add(dataRow);
            }
 
            return table;
        }

You can create a static class for extension methods and put it there.
To convert Year values to columns and get Pivot DataTable:

var pivotTable = data.ToPivotTable(
              item => item.Year, 
              item => item.Product,  
              items => items.Any() ? items.Sum(x=>x.Sales) : 0);

You will get the following output:

C# Array to Pivot Dynamic Array:

You might want to get the List<dynamic> or dynamic[] instead of getting DataTable after converting columns to rows. It is handy in ASP.NET Web API to return JSON response.

To do it, I updated the extension method to get the dynamic object. use following extension method:

public static dynamic[] ToPivotArray<T, TColumn, TRow, TData>(
this IEnumerable<T> source,
Func<T, TColumn> columnSelector,
Expression<Func<T, TRow>> rowSelector,
Func<IEnumerable<T>, TData> dataSelector)
       {
 
           var arr = new List<object>();
           var cols = new List<string>();
           String rowName = ((MemberExpression)rowSelector.Body).Member.Name;
           var columns = source.Select(columnSelector).Distinct();       
 
           cols =(new []{ rowName}).Concat(columns.Select(x=>x.ToString())).ToList();
 
 
           var rows = source.GroupBy(rowSelector.Compile())
                            .Select(rowGroup => new
                            {
                                Key = rowGroup.Key,
                                Values = columns.GroupJoin(
                                    rowGroup,
                                    c => c,
                                    r => columnSelector(r),
                                    (c, columnGroup) => dataSelector(columnGroup))
                            }).ToArray();
 
 
           foreach (var row in rows)
           {
               var items = row.Values.Cast<object>().ToList();
               items.Insert(0, row.Key);
               var obj = GetAnonymousObject(cols, items);
               arr.Add(obj);               
           }
           return arr.ToArray();
       }
 private static dynamic GetAnonymousObject(IEnumerable<string> columns, IEnumerable<object> values)
       {
           IDictionary<string, object> eo = new ExpandoObject() as IDictionary<string, object>;
           int i;
           for (i = 0; i < columns.Count(); i++)
           {
               eo.Add(columns.ElementAt<string>(i), values.ElementAt<object>(i));
           }
           return eo;
       }

ExpandoObject is used to create dynamic object.
Now, to convert row to column and get dynamic array:

var pivotArray = data.ToPivotArray(
                item => item.Year,
               item => item.Product,
               items => items.Any() ? items.Sum(x => x.Sales) : 0);

You can easily convert in JSON format

String json = JsonConvert.SerializeObject(pivotArray, new KeyValuePairConverter());

C# DataTable to Pivot DataTable:

Let us have a DataTable with same data:

DataTable myDataTable = new DataTable();
myDataTable.Columns.AddRange(new DataColumn[3] { new DataColumn("Product"), new DataColumn("Year", typeof(int)), new DataColumn("Sales", typeof(int)) });
myDataTable.Rows.Add("Product 1", 2009, 1212);
myDataTable.Rows.Add("Product 2", 2009, 522);
myDataTable.Rows.Add("Product 1", 2010, 1337);
myDataTable.Rows.Add("Product 2", 2011, 711);
myDataTable.Rows.Add("Product 2", 2012, 2245);
myDataTable.Rows.Add("Product 3", 2012, 1000);   

You can use the same extension method to get Pivot DataTable like below.

var data2 = myDataTable.AsEnumerable().Select(x=> new { 
               Product =x.Field<String>("Product"), 
               Year= x.Field<int>("Year"), 
               Sales = x.Field<int>("Sales") });
           
           DataTable pivotDataTable =data2.ToPivotTable(
                item => item.Year,
               item => item.Product,
               items => items.Any() ? items.Sum(x => x.Sales) : 0);

Here is the result:

DataTable to List<dynamic>:

If you need to convert DataTable to List of dynamic object then use following extension method:

public static List<dynamic> ToDynamicList(this DataTable dt)
       {
           var list = new List<dynamic>();
           foreach (DataRow row in dt.Rows)
           {
               dynamic dyn = new ExpandoObject();
               list.Add(dyn);
               foreach (DataColumn column in dt.Columns)
               {
                   var dic = (IDictionary<string, object>)dyn;
                   dic[column.ColumnName] = row[column];
               }
           }
           return list;
       }

Here is the result:

Group by Multiple Columns

Here is a code to group data based on multiple columns.

testDt = GetTestDate();
        var data2 = testDt.Tables[0].AsEnumerable().Select(x => new
        {
            Family = x.Field<int>("tdFamily"),
            Class = x.Field<short>("luClass"),
            Region = x.Field<short>("luRegion"),
            Year = x.Field<int>("tdYear"),
            Population = x.Field<decimal>("tdPopulation ")
        });

        DataTable pivotDataTable = data2.ToPivotTable(
             item => item.Year,
            item => new{ item.Family, item.Class, item.Region},
            items => items.Any() ? items.Sum(x => x.Allocation) : 0
            );

         public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
         this IEnumerable<T> source,
         Func<T, TColumn> columnSelector,
         Expression<Func<T, TRow>> rowSelector,
         Func<IEnumerable<T>, TData> dataSelector)
    {
        DataTable table = new DataTable();
        var rowsName = ((NewExpression)rowSelector.Body).Members.Select(s => s).ToList();
        foreach (var row in rowsName)
        {
            var name = row.Name; 
            table.Columns.Add(new DataColumn(name));
        }
        var columns = source.Select(columnSelector).Distinct();
        foreach (var column in columns)
            table.Columns.Add(new DataColumn(column.ToString()));
        var rows = source.GroupBy(rowSelector.Compile())
                         .Select(rowGroup => new
                         {
                             Key = rowGroup.Key,
                             Values = columns.GroupJoin(
                                 rowGroup,
                                 c => c,
                                 r => columnSelector(r),
                                 (c, columnGroup) => dataSelector(columnGroup))
                         });

        foreach (var row in rows)
        {
            var dataRow = table.NewRow();
            var items = row.Values.Cast<object>().ToList();
            string[] keyRow = row.Key.ToString().Split(',');
            int index = 0;
            foreach (var key in keyRow)
            {
                string keyValue = key.Replace("}", "").Split('=')[1].Trim();
                items.Insert(index, keyValue);
                index++;
            }
            dataRow.ItemArray = items.ToArray();
            table.Rows.Add(dataRow);
        }
        return table;
    }

Conclusion:

In this post, we played with C# Array, DataTable and implemented to convert row to column and get Pivot Array, DataTable and List of dynamic object.