The Problem
You have a data table and you need to group and sum records dynamically.
Solution
I needed to group information by a field and the sum the values on a different field;
For example, I want to group employees by department and then sum their salary.
Below is the linq code to this:
PivotFieldName is the name of the department field on the data table
FieldName is the name of the salary field on the data table
VB. Net
For VB.Net you can use anonymous types to set field type and dynamically set it at run time.
if odata.Rows.Count = 0 Then Return nothing
Dim row = odata.Rows(0)
Dim fieldType As Type = row(fieldName).GetType()
Dim query
query = From record In oData.AsEnumerable()
Where Not record.Field(Of Integer?)(“PivotFieldName”) Is Nothing
Group record By pivotfieldName= record.Field(Of Integer) (“PivotFieldName”) Into myGroup = Group
Select New With {
Key myDuration,
.total = myGroup.Sum(Function(r) r.Field(Of fieldType)(fieldname))
}
C#
For C#, you cannot replace the value of the field type at run time, so for now, I am just using integer. For more numeric types, or string types, we have to create an Interface and then Implement a Strategy / Pattern design/
if (_odata.Rows.Count == 0)
return null;
var query = from records in _odata.AsEnumerable()
group records by records.Field(“PivotFieldName”) into g
select new
{
yearDuration = g.Key,
total = g.Sum(records => records.Field(fieldname))
};
return query;
Serendipituos Code 