Sum and Group Data Table Records Using Linq

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;


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s