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.