in Education by
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("ColumnName")) .Select(g => new { ColumnName = g.Key, a = g.Where(row => row.Field("Property") == "a").Select(c => c.Field("Value")), b = g.Where(row => row.Field("Property") == "b").Select(c => c.Field("Value")), c = g.Where(row => row.Field("Property") == "c").Select(c => c.Field("Value")), d = g.Where(row => row.Field("Property") == "d").Select(c => c.Field("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
Try this: class Program { //Helper method to make the Select cleaner: private static string GetProperty(IEnumerable rows, string propertyName) { return rows .Where(row => row.Field("Property") == propertyName) .Select(c => c.Field("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("ObjectName"), ColumnName = row.Field("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; /// /// Code copied directly from http://msdn.microsoft.com/en-us/library/bb669096.aspx /// /// public class ObjectShredder { private System.Reflection.FieldInfo[] _fi; private System.Reflection.PropertyInfo[] _pi; private System.Collections.Generic.Dictionary _ordinalMap; private System.Type _type; // ObjectShredder constructor. public ObjectShredder() { _type = typeof(T); _fi = _type.GetFields(); _pi = _type.GetProperties(); _ordinalMap = new Dictionary(); } /// /// Loads a DataTable from a sequence of objects. /// /// The sequence of objects to load into the DataTable. /// 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. /// Specifies how values from the source sequence will be applied to /// existing rows in the table. /// A DataTable created from the source sequence. public DataTable Shred(IEnumerable 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 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 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 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; } } /// /// Code copied directly from http://msdn.microsoft.com/en-us/library/bb669096.aspx /// public static class CustomLINQtoDataSetMethods { public static DataTable CopyToDataTable(this IEnumerable source) { return new ObjectShredder().Shred(source, null, null); } public static DataTable CopyToDataTable(this IEnumerable source, DataTable table, LoadOption? options) { return new ObjectShredder().Shred(source, table, options); } }

Related questions

0 votes
    I have a blade template that creates a form. The form uses a foreach loop to iterate through a ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 10, 2022 in Education by JackTerrance
0 votes
    Hi I am having problems with relation and database design. There are three tables called 'articles' , ' ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 24, 2022 in Education by JackTerrance
0 votes
    I have a blade template that creates a form. The form uses a foreach loop to iterate through a ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 22, 2022 in Education by JackTerrance
0 votes
    I am trying to create a pandas dataframe that combines all children into one row class Parent(Base): ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 16, 2022 in Education by JackTerrance
0 votes
    In the code below I pivot a dataframe using an index date. After the pivot, I need to get the ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 5, 2022 in Education by JackTerrance
0 votes
    ___________ is the core business logic on which CorDapps run. A. Transaction B. Contract C. Legal prose D. Flow framework...
asked Feb 23, 2023 in Technology by JackTerrance
0 votes
    I have a menu which opens up a submenu on click. If a link has a dropdown menu (has class header ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 7, 2022 in Education by JackTerrance
0 votes
    I have a menu which opens up a submenu on click. If a link has a dropdown menu (has class header ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 2, 2022 in Education by JackTerrance
0 votes
    I want to disable the sort property in dataTable API. I use bsort:false to disable sorting from all ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jul 20, 2022 in Education by JackTerrance
0 votes
    Apologies if this question is answered elsewhere on StackOverflow, I did my best to search for an answer ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 13, 2022 in Education by JackTerrance
0 votes
    I am using datatable with Laravel to order a response that i receive from an API I connect to the ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 23, 2022 in Education by JackTerrance
0 votes
    I currently use a DataTable to get results from a database which I can use in my code. However, ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 27, 2022 in Education by JackTerrance
0 votes
    ok I am trying to use the f:param here to pass the requestid as parameter to the review page. ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 11, 2022 in Education by JackTerrance
0 votes
    I am using this nuget UiPathTeam.WpfFormCreator.Activities, through which we could show a xaml form for user input by an ... } } }} Select the correct answer from above options...
asked Feb 8, 2022 in Education by JackTerrance
0 votes
    I want to search a column of a datatable after values from a textbox. I want to search after ISBN ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 7, 2022 in Education by JackTerrance
...