I have a DataTable in VB.NET of this type:
"Yr","Mnth","Period","Amount"
2016, 1, 2016-01, 550.36
2016, 1, 2016-01, 9000.79
2015, 12, 2015-12, 10000.30
2015, 12, 2015-12, 20
What I want to do is to aggregate this data using LINQ like I would in SQL language:
SELECT Yr, Mnth, Period, SUM(Amount) AS Amount
GROUP BY Yr, Mnth, Period;
I have tried to use LINQ in VB.NET but haven't been able to get it right. Can someone give me some insight? Thank you.
Dim Amounts As New DataTable 'Your code to load actual DataTable here
Dim amountGrpByDates = From row In Amounts
Group row By dateGroup = New With {
Key .Yr = row.Field(Of Integer)("Yr"),
Key .Mnth = row.Field(Of Integer)("Mnth"),
Key .Period = row.Field(Of String)("Period")
} Into Group
Select New With {
Key .Dates = dateGroup,
.SumAmount = Group.Sum(Function(x) x.Field(Of Decimal)("Amount"))}
I assumed Yr and Mnth to be of type Integer, Period String and Amount Decimal.
Change the types if they differ from yours.
Here's the C# code.
public static class program
{
static void Main(string[] args)
{
try
{
var table = new DataTable();
table.Columns.Add("year", typeof(string));
table.Columns.Add("month", typeof(string));
table.Columns.Add("period", typeof(string));
table.Columns.Add("amount", typeof(decimal));
var row = table.NewRow();
table.Rows.Add(row);
row["year"] = "2015";
row["month"] = "Jan";
row["period"] = "Period1";
row["amount"] = 100;
row = table.NewRow();
table.Rows.Add(row);
row["year"] = "2015";
row["month"] = "Jan";
row["period"] = "Period1";
row["amount"] = 50;
row = table.NewRow();
table.Rows.Add(row);
row["year"] = "2016";
row["month"] = "Fed";
row["period"] = "Period2";
row["amount"] = 5.55;
var result = (from r in table.AsEnumerable()
group r by new
{
Year = r.Field<string>("year"),
Month = r.Field<string>("month"),
Period = r.Field<string>("period"),
} into grp
select new {
grp.Key,
total = grp.Sum(p=> p.Field<decimal>("amount"))
});
foreach(var grp in result)
{
Console.WriteLine("{0} {1} {2} = {3}", grp.Key.Year, grp.Key.Month, grp.Key.Period, grp.total);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
DataTableor can you group the source data instead?