Tuesday, April 8, 2014

Apply LINQ Grouping and Sum aggregate to a Datatable Object (VB.NET)

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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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

Output:
See C# Version here: Apply LINQ Grouping and Sum aggregate to a Datatable Object (C#)

2 comments: