Apply LINQ Grouping And Sum Aggregate To A Datatable Object (VB.NET)

- April 08, 2014

Good day!

There's a question on vb forums on how to apply LINQ grouping and aggregate function sum to a datatable object. This can be achieved by familiarizing on IGrouping which is a key/value pair interface. Here's how i did it.

VB.NET

Dim dt As New DataTable("tblEntTable")
dt.Columns.Add("ID", GetType(String))
dt.Columns.Add("amount", GetType(Decimal))
dt.Rows.Add(New Object() {"1", 100.51})
dt.Rows.Add(New Object() {"1", 200.52})
dt.Rows.Add(New Object() {"2", 500.24})
dt.Rows.Add(New Object() {"2", 400.31})
dt.Rows.Add(New Object() {"3", 600.88})
dt.Rows.Add(New Object() {"3", 700.11})

Dim result = (From orders In dt.AsEnumerable
    Group orders By ID = orders.Field(Of String)("ID") Into g = Group
    Select New With { Key ID,
        .Amount = g.Sum(Function(r) r.Field(Of Decimal)("amount"))
    }).OrderBy(Function(tkey) tkey.ID).ToList()

dgvSum.DataSource = result