0 votes
in Education by (1.3m points)
So I've seen a few posts describing this, but can't quite figure it out for my case. I used to have a SQL query that used the PIVOT command to order my table, I am trying to move this logic into our app via LINQ. The table is stored in a DataTable and looks like this.

ObjectName | ColumnName  |  Property  |  Value

----------------------------------------------

foo        | bar         |  a         | w

foo        | bar         |  b         | x

foo        | bar         |  c         | y

foo        | bar         |  d         | z

foo        | test        |  a         | i

foo        | test        |  b         | j

foo        | test        |  c         | k

foo        | test        |  d         | l

I want to morph it into a DataTable that looks like this.

ObjectName   |  ColumnName  |  a  |  b  |  c  |  d

---------------------------------------------------

foo          |  bar         |  w  |  x  |  y  |  z

foo          |  test        |  i  |  j  |  k  |  l

So I tried something like this...

var query = dt.AsEnumerable()

    .GroupBy(row => row.Field<string>("ColumnName"))

    .Select(g => new {

        ColumnName = g.Key,

        a = g.Where(row => row.Field<string>("Property") == "a").Select(c => c.Field<string>("Value")),

        b = g.Where(row => row.Field<string>("Property") == "b").Select(c => c.Field<string>("Value")),

        c = g.Where(row => row.Field<string>("Property") == "c").Select(c => c.Field<string>("Value")),

        d = g.Where(row => row.Field<string>("Property") == "d").Select(c => c.Field<string>("Value"))

    });

Which doesn't include ObjectName (for some reason was giving me a compile error to add it?). Looking at the debugger ColumnName is showing up right, but the rest is mostly gibberish. Sorry my LINQ skills are pretty sub-par, I'm trying to learn, but get confused easily.

I'm guessing my datatype isn't coming out correctly to be able to use that extension method, but I'm in a bit over my head. Any suggestions?

Edit still getting some errors, I am fighting with this line

DataTable newDT = query.CopyToDataTable();

but I get the error message

The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable (System.Collections.Generic.IEnumerable)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'.

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.3m points)
Try this:

class Program

{

//Helper method to make the Select cleaner:

private static string GetProperty(IEnumerable<DataRow> rows, string propertyName)

{

    return rows

        .Where(row => row.Field<string>("Property") == propertyName)

        .Select(c => c.Field<string>("Value"))

        .FirstOrDefault();

}

//helper method for populating the datatable

private static void addRow(DataTable dt, string objectName, string columnName

    , string property, string value)

{

    var row = dt.NewRow();

    row["ObjectName"] = objectName;

    row["ColumnName"] = columnName;

    row["Property"] = property;

    row["Value"] = value;

    dt.Rows.Add(row);

}

public static void Main(string[] args)

{

    DataTable dt = new DataTable();

    dt.Columns.Add("ObjectName");

    dt.Columns.Add("ColumnName");

    dt.Columns.Add("Property");

    dt.Columns.Add("Value");

    addRow(dt, "foo", "bar", "a", "w");

    addRow(dt, "foo", "bar", "b", "x");

    addRow(dt, "foo", "bar", "c", "y");

    addRow(dt, "foo", "bar", "d", "z");

    addRow(dt, "foo", "test", "a", "i");

    addRow(dt, "foo", "test", "b", "j");

    addRow(dt, "foo", "test", "c", "k");

    addRow(dt, "foo", "test", "d", "l");

    var query = dt.AsEnumerable()

        .GroupBy(row => new

        {

            ObjectName = row.Field<string>("ObjectName"),

            ColumnName = row.Field<string>("ColumnName")

        })

        .Select(g => new

        {

            ObjectName = g.Key.ObjectName,

            ColumnName = g.Key.ColumnName,

            a = GetProperty(g, "a"),

            b = GetProperty(g, "b"),

            c = GetProperty(g, "c"),

            d = GetProperty(g, "d"),

        })

        .CopyToDataTable();

    foreach (DataRow row in query.Rows)

    {

        foreach (DataColumn column in query.Columns)

        {

            System.Console.Write(row[column] + "\t");

        }

        System.Console.WriteLine();

    }

    Console.WriteLine("Press any key to exit. . .");

    Console.ReadKey(true);

}

}

Here is the code that I'm using to copy to the datattable, since you didn't state what you were using:

using System;

using System.Data;

using System.Collections.Generic;

using System.Reflection;

/// <summary>

/// Code copied directly from http://msdn.microsoft.com/en-us/library/bb669096.aspx

/// </summary>

/// <typeparam name="T"></typeparam>

public class ObjectShredder<T>

{

    private System.Reflection.FieldInfo[] _fi;

    private System.Reflection.PropertyInfo[] _pi;

    private System.Collections.Generic.Dictionary<string, int> _ordinalMap;

    private System.Type _type;

    // ObjectShredder constructor.

    public ObjectShredder()

    {

        _type = typeof(T);

        _fi = _type.GetFields();

        _pi = _type.GetProperties();

        _ordinalMap = new Dictionary<string, int>();

    }

    /// <summary>

    /// Loads a DataTable from a sequence of objects.

    /// </summary>

    /// <param name="source">The sequence of objects to load into the DataTable.</param>

    /// <param name="table">The input table. The schema of the table must match that

    /// the type T.  If the table is null, a new table is created with a schema

    /// created from the public properties and fields of the type T.</param>

    /// <param name="options">Specifies how values from the source sequence will be applied to

    /// existing rows in the table.</param>

    /// <returns>A DataTable created from the source sequence.</returns>

    public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options)

    {

        // Load the table from the scalar sequence if T is a primitive type.

        if (typeof(T).IsPrimitive)

        {

            return ShredPrimitive(source, table, options);

        }

        // Create a new table if the input table is null.

        if (table == null)

        {

            table = new DataTable(typeof(T).Name);

        }

        // Initialize the ordinal map and extend the table schema based on type T.

        table = ExtendTable(table, typeof(T));

        // Enumerate the source sequence and load the object values into rows.

        table.BeginLoadData();

        using (IEnumerator<T> e = source.GetEnumerator())

        {

            while (e.MoveNext())

            {

                if (options != null)

                {

                    table.LoadDataRow(ShredObject(table, e.Current), (LoadOption)options);

                }

                else

                {

                    table.LoadDataRow(ShredObject(table, e.Current), true);

                }

            }

        }

        table.EndLoadData();

        // Return the table.

        return table;

    }

    public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options)

    {

        // Create a new table if the input table is null.

        if (table == null)

        {

            table = new DataTable(typeof(T).Name);

        }

        if (!table.Columns.Contains("Value"))

        {

            table.Columns.Add("Value", typeof(T));

        }

        // Enumerate the source sequence and load the scalar values into rows.

        table.BeginLoadData();

        using (IEnumerator<T> e = source.GetEnumerator())

        {

            Object[] values = new object[table.Columns.Count];

            while (e.MoveNext())

            {

                values[table.Columns["Value"].Ordinal] = e.Current;

                if (options != null)

                {

                    table.LoadDataRow(values, (LoadOption)options);

                }

                else

                {

                    table.LoadDataRow(values, true);

                }

            }

        }

        table.EndLoadData();

        // Return the table.

        return table;

    }

    public object[] ShredObject(DataTable table, T instance)

    {

        FieldInfo[] fi = _fi;

        PropertyInfo[] pi = _pi;

        if (instance.GetType() != typeof(T))

        {

            // If the instance is derived from T, extend the table schema

            // and get the properties and fields.

            ExtendTable(table, instance.GetType());

            fi = instance.GetType().GetFields();

            pi = instance.GetType().GetProperties();

        }

        // Add the property and field values of the instance to an array.

        Object[] values = new object[table.Columns.Count];

        foreach (FieldInfo f in fi)

        {

            values[_ordinalMap[f.Name]] = f.GetValue(instance);

        }

        foreach (PropertyInfo p in pi)

        {

            values[_ordinalMap[p.Name]] = p.GetValue(instance, null);

        }

        // Return the property and field values of the instance.

        return values;

    }

    public DataTable ExtendTable(DataTable table, Type type)

    {

        // Extend the table schema if the input table was null or if the value

        // in the sequence is derived from type T.            

        foreach (FieldInfo f in type.GetFields())

        {

            if (!_ordinalMap.ContainsKey(f.Name))

            {

                // Add the field as a column in the table if it doesn't exist

                // already.

                DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name]

                    : table.Columns.Add(f.Name, f.FieldType);

                // Add the field to the ordinal map.

                _ordinalMap.Add(f.Name, dc.Ordinal);

            }

        }

        foreach (PropertyInfo p in type.GetProperties())

        {

            if (!_ordinalMap.ContainsKey(p.Name))

            {

                // Add the property as a column in the table if it doesn't exist

                // already.

                DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]

                    : table.Columns.Add(p.Name, p.PropertyType);

                // Add the property to the ordinal map.

                _ordinalMap.Add(p.Name, dc.Ordinal);

            }

        }

        // Return the table.

        return table;

    }

}

/// <summary>

/// Code copied directly from http://msdn.microsoft.com/en-us/library/bb669096.aspx

/// </summary>

public static class CustomLINQtoDataSetMethods

{

    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)

    {

        return new ObjectShredder<T>().Shred(source, null, null);

    }

    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source,

                                                DataTable table, LoadOption? options)

    {

        return new ObjectShredder<T>().Shred(source, table, options);

    }

}

Learn More with Blogmepost

blogmepost Online Test

NCERT Questions & Answers

  • NCERT Class 12 Maths Solutions
  • NCERT Class 12 Physics Solutions
  • NCERT Class 12 Chemistry Solutions
  • NCERT Class 12 Biology Solutions
  • NCERT Class 10 Science Solutions
  • NCERT Class 10 Maths Solutions
blogmepost Q&A

CBSE Questions & Answers Portal

  • CBSE Class 12 Maths Questions Answers
  • CBSE Class 12 Physics Questions Answers
  • CBSE Class 12 Chemistry Questions Answers
  • CBSE Class 12 Biology Questions Answers
  • CBSE Class 10 Science Questions Answers
  • CBSE Class 10 Maths Questions Answers
Blogmepost

Learn Math Formula, Revise Notes

  • JEE Crash Course Questions & Answers
  • NCERT Course Questions and Answers
  • CBSE Board Questions Bank
  • ICSE Board Questions Bank
  • Free Study Materials
  • Question & Answers Bank for All Classes
  • Text Book Solutions
  • Previous Year Question Paper
  • NCERT Book Solutions

  • NCERT
  • NCERT Solutions
  • NCERT Solutions for Class 12 Maths
  • NCERT Solutions for Class 12 Physics
  • NCERT Solutions for Class 12 Chemistry
  • NCERT Solutions for Class 12 Biology
  • NCERT Solutions for Class 11 Maths
  • NCERT Solutions for Class 11 Physics
  • NCERT Solutions for Class 11 Chemistry
  • NCERT Solutions for Class 11 Biology
  • NCERT Solutions for Class 10 Maths
  • NCERT Solutions for Class 10 Science
  • NCERT Solutions for Class 9 Maths
  • NCERT Solutions for Class 9 Science
  • CBSE Classes

  • CBSE Class 5 Questions
  • CBSE Class 6 Questions
  • CBSE Class 7 Questions
  • CBSE Class 8 Questions
  • CBSE Class 9 Questions
  • CBSE Class 10 Questions
  • CBSE Class 10 Maths Questions
  • CBSE Class 11 Commerce Questions
  • CBSE Class 11 Engineering Questions
  • CBSE Class 11 Medical Questions
  • CBSE Class 12 Commerce Questions
  • CBSE Class 12 Maths Questions
  • CBSE Class 12 Engineering Questions
  • CBSE Class 12 Medical Questions
  • BOARDS

  • CBSE
  • ICSE
  • NCERT
  • IGCSE
  • Andhra Pradesh
  • Bihar
  • Gujarat
  • Jharkhand
  • Karnataka
  • Kerala
  • Madhya Pradesh
  • Maharashtra
  • Punjab
  • Rajasthan
...