Grouping and Aggregating When Querying LINQ to SQL - Aggregations in LINQ (like GROUP BY of SQL in LINQ): continued
(Page 4 of 5 )
Now, let us consider the following SQL SELECT query:
SELECT CustomerID, SUM(Freight) FROM Orders GROUP BY CustomerID
In the previous section, we simply counted the rows (or objects in the case of LINQ). Now, we need to have some calculation with the Freight column.
The above SELECT can be written in LINQ (VB.NET) as follows:
Dim q = From p In db.Orders _
Group p By p.CustomerID _
Into Sum(p.Freight) _
Select _
CustomerID, _
FreightSum = Sum
In the above you can observe that I am passing the field “Freight” in the “Into” clause. The same in C# LINQ would be as follows:
var q = from p in db.Orders
group p.Freight by p.CustomerID
into g
select new { CustomerID = g.Key, FreightSum = g.Sum() };
Make a note of the “group” clause in the above statement (it includes “Freight”). The other ways to write the same in VB.NET LINQ are as follows:
Dim q = From p In db.Orders _
Group p By p.CustomerID _
Into Group _
Select _
CustomerID, _
FreightSum = Group.Sum(Function(p) p.Freight)
Dim q = From p In db.Orders _
Group By p.CustomerID _
Into FreightSum = Sum(p.Freight)
We can also combine aggregations of different columns with different computations as shown in following SQL SELECT:
SELECT CustomerID, COUNT(*), SUM(Freight), MAX(Freight)
FROM Orders
GROUP BY CustomerID
The above SELECT can be written in VB.NET LINQ as follows:
Dim q = From p In db.Orders _
Group p By p.CustomerID _
Into _
NoOfOrders = Count(), _
FreightSum = Sum(p.Freight), _
HighestFreight = Max(p.Freight) _
Select _
CustomerID, _
NoOfOrders, _
FreightSum, _
HighestFreight
In C# LINQ, it would be as follows:
var q = from p in db.Orders
group p.Freight by p.CustomerID
into g
select new {
CustomerID = g.Key,
NoOfOrders=g.Count(),
FreightSum = g.Sum(),
HighestFreight = g.Max()
};
And further, we can do more with VB.NET LINQ as follows:
Dim q = From p In db.Orders _
Group p By p.CustomerID _
Into Group _
Select _
CustomerID, _
NoOfOrders = Group.Count, _
FreightSum = Group.Sum(Function(p) p.Freight), _
HighestFreight = Group.Max(Function(p) p.Freight)
Dim q = From p In db.Orders _
Group By p.CustomerID _
Into _
NoOfOrders = Count(), _
FreightSum = Sum(p.Freight), _
FreightMax = Max(p.Freight)
Next: Aggregations in LINQ (like GROUP BY..HAVING of SQL in LINQ) >>
More .NET Articles
More By Jagadish Chaterjee
/ 9 
