How to change the data layout of a data table with LINQ

Question

enter image description here

Above is the screen shot of one of my Data Table. I am trying to transform this data into the following format so that I can bind it to one of my grid. I have tried LINQ but unsuccessful. Could please anyone help me how I can do this. Doesn't necessarily be LINQ but I think it will be easier with LINQ

enter image description here

First I am getting the Distinct Columns from "Object" as follow var distinctColumnNames = dataSet.Tables["reportColumns"].AsEnumerable() .Select(row => new { colObject = row.Field("Object"), }).Distinct(); — Learning Curve, Aug 06 '13 at 10:02
then I am running the following Linq statement foreach (var columnName in distinctColumnNames) { var dataRow = dataSet.Tables["reportColumns"].AsEnumerable() .Select(rowData => new { colAttribute = rowData.Field("Attribute"), colValue = rowData.Field("Value"), colStandard = rowData.Field("Standard") }); } — Learning Curve, Aug 06 '13 at 10:04
the bit I am missing is how to apply where clause in second statement (e.g. where dataRow.Object == "columnName"). I know i dont have the Object column selected over here but after selecting that column in dataRow how the where clause will be applied? — Learning Curve, Aug 06 '13 at 10:06
I have changed my second Linq Select statement and have added the "Object" column foreach (var columnName in distinctColumnNames) { var dataRow = dataSet.Tables["reportColumns"].AsEnumerable() .Select(rowData => new { colObject = rowData.Field("Object"), colAttribute = rowData.Field("Attribute"), colValue = rowData.Field("Value"), colStandard = rowData.Field("Standard") }); } — Learning Curve, Aug 06 '13 at 10:08

Damith · Accepted Answer · 2013-08-06T10:16:47.467

0

try below

var result =  dataSet.Tables["reportColumns"].AsEnumerable().GroupBy(x => x.Field<string>("Object"))
    .Select(g => new
    {
        ColumnName = g.Key,
        DefaultColumn = g.FirstOrDefault(p => p.Field<string>("Attribute") == "DefaultColumn").Field<string>("Value"),
        Label = g.FirstOrDefault(p => p.Field<string>("Attribute") == "Label").Field<string>("Value"),
        Type = g.FirstOrDefault(p => p.Field<string>("Attribute") == "Type").Field<string>("Value"),
        Standard = g.FirstOrDefault().Field<int>("Standard")
    }).ToList();

edited Aug 06 '13 at 10:16

answered Aug 06 '13 at 10:10

Damith

62,401
13
102
153

Thank you very much Damith. the result has brought a big smile on my face. Really appreciate your time and effort. – Learning Curve Aug 06 '13 at 10:23

score 0 · Answer 2 · edited May 23 '17 at 11:50

You can use my ToPivotTable extension:

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

Create strongly-typed data from your source table:

var data = from r in table.AsEnumerable()
           select new {
                Object = r.Field<string>("Object"),
                Attribute = r.Field<string>("Attribute"),
                Value = r.Field<object>("Value")
           };

And convert them to pivot table:

var pivotTable = data.ToPivotTable(r => r.Attribute, 
                                   r => r.Object, 
                                   rows => rows.First().Value);

This will create pivot table with distinct values of Attribute (i.e. DefaultColumn, Label, Type) as columns, rows will be groups for each Object value, and each cell will have value of corresponding Value property for object group and attribute column.

Or in single query:

var pivotTable = table.AsEnumerable()
                      .Select(r => new {
                          Object = r.Field<string>("Object"),
                          Attribute = r.Field<string>("Attribute"),
                          Value = r.Field<object>("Value")
                      })
                      .ToPivotTable(r => r.Attribute, 
                                    r => r.Object, 
                                    rows => rows.First().Value);

Hi Lazyberezovsky, thank you very much for your reply. I haven't tried your sample code as I was already doing the LINQ way so decided to follow Damith approach and it worked for me. — Learning Curve, Aug 06 '13 at 10:24
@waseem this *is* a Linq way. And it does not requires three queries for each object group — Sergey Berezovskiy, Aug 06 '13 at 10:29

How to change the data layout of a data table with LINQ

2 Answers2