C # using linq to group across multiple columns in a datatable

Question

I have three columns in a datatable: string, DateTime and decimal. I want to group by row column and decimal table, and for grouped rows I want to sum the decimal values. I know how to make a sum, but how do you group two different columns in a datatable?

This is my code so far that is not working properly:

var newSort = from row in objectTable.AsEnumerable() group row by new {ID = row.Field<string>("resource_name"), time1 = row.Field<DateTime>("day_date")} into grp orderby grp.Key select new { resource_name1 = grp.Key.ID, day_date1 = grp.Key.time1, Sum = grp.Sum(r => r.Field<Decimal>("actual_hrs")) }; 

Answer 1

I don’t think you are telling us the whole story. In addition to orderby , which does not work with anonymous types (the code you gave was not compiled), your request should work the way you want. I just put this in LINQPad:

 var objectTable = new DataTable(); objectTable.Columns.Add("resource_name",typeof(string)); objectTable.Columns.Add("day_date",typeof(DateTime)); objectTable.Columns.Add("actual_hrs",typeof(decimal)); objectTable.Rows.Add(1, DateTime.Today, 1); objectTable.Rows.Add(2, DateTime.Today, 2); var newSort = from row in objectTable.AsEnumerable() group row by new {ID = row.Field<string>("resource_name"), time1 = row.Field<DateTime>("day_date")} into grp select new { resource_name1 = grp.Key.ID, day_date1 = grp.Key.time1, Sum = grp.Sum(r => r.Field<Decimal>("actual_hrs")) }; newSort.Dump(); 

... and I got the following results:

 resource_name1 | day_date1 | Sum 1 | 7/1/2011 12:00:00 AM | 1 2 | 7/1/2011 12:00:00 AM | 2 

Answer 2

use this code

 var newSort = from row in objectTable.AsEnumerable() group row by new {ID = row.Field<string>("resource_name"), time1 = row.Field<DateTime>("day_date")} into grp orderby grp.Key select new { resource_name1 = grp.Key.ID, day_date1 = grp.Key.time1, Sum = grp.Sum(r => Convert.ToDecimal(r.ItemArray[2])) }; 

Answer 3

For those who want a solution on Vb.net, here is an example:

 Dim workTable As DataTable = New DataTable("Customers") Dim workCol As DataColumn = workTable.Columns.Add("ID", Type.GetType("System.Int32")) workTable.Columns.Add("Total", Type.GetType("System.Decimal")) workTable.Columns.Add("Compra", Type.GetType("System.Decimal")) Dim row As DataRow = workTable.NewRow() row("id") = 2 row("total") = 1.5 row("compra") = 3 workTable.Rows.Add(row) row = workTable.NewRow() row("id") = 1 row("total") = 1.5 row("compra") = 3.3999999999999999 workTable.Rows.Add(row) row = workTable.NewRow() row("id") = 1 row("total") = 1.5 row("compra") = 5 workTable.Rows.Add(row) Dim detalles As IEnumerable(Of DataRow) = workTable.AsEnumerable() Dim query = From detalle In detalles.AsEnumerable() _ Group detalle By grupoClave = New With _ { _ Key .C2 = detalle("id"), _ Key .C4 = detalle("total")} Into g = Group _ Select New With _ { _ .Col2 = g(0).Field(Of Integer)("id"), _ .Col3 = g(0).Field(Of Decimal)("total"), _ .Col4 = g.Sum(Function(fact) fact.Field(Of Decimal)("compra")) _ } For Each p In query Console.WriteLine((p.Col2 & p.Col3 & p.Col4)) Next