c # using linq to group by multiple columns in a datatable
76,266
Solution 1
I don't think you're giving us the full story. Other than orderby
not working with anonymous types (the code you gave wouldn't have compiled), your query 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 these results:
resource_name1 | day_date1 | Sum
1 | 7/1/2011 12:00:00 AM | 1
2 | 7/1/2011 12:00:00 AM | 2
Solution 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]))
};
Author by
Sam
Updated on July 15, 2020Comments
-
Sam almost 4 years
I have three columns in a datatable: string, DateTime, and decimal. I want to group by the string and decimal column, and for the rows grouped I want to sum the decimal values. I know how to do the sum part, but how do you group two different columns in a datatable?
This is my code so far which doesn't work 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")) };
-
Si8 almost 7 yearsCurious to know how can I pull one row from each group from the above based on the MIN value of two other columns combined?
-
StriplingWarrior almost 7 years@Si8:
grp.OrderBy(g => g.col1 + g.col2).FirstOrDefault()
?