Grouping and Aggregating When Querying LINQ to SQL - Aggregations in LINQ (like GROUP BY..HAVING of SQL in LINQ)
(Page 5 of 5 )
Now, let us consider the following SQL SELECT query:
SELECT CustomerID, SUM(Freight)
FROM Orders
GROUP BY CustomerID
HAVING SUM(Freight) > 200
The above does the aggregation first and then filters the output, applying the HAVING condition. The same can be achieved in VB.NET LINQ as follows:
Dim q = From p In db.Orders _
Group p By p.CustomerID _
Into FreightSum = Sum(p.Freight) _
Where FreightSum > 200 _
Select _
CustomerID, _
FreightSum
And in C# LINQ as follows:
var q = from p in db.Orders
group p.Freight by p.CustomerID
into g
where g.Sum() > 200
select new { CustomerID = g.Key, FreightSum = g.Sum() };
Further flavors for the same in VB.NET LINQ are as follows:
Dim q = From p In db.Orders _
Group p By p.CustomerID _
Into Group _
Where Group.Sum(Function(p) p.Freight) > 200 _
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) _
Where FreightSum > 200
Dim q = From p In db.Orders _
Group FreightValue = p.Freight By p.CustomerID _
Into FreightSum = Sum(FreightValue) _
Where FreightSum > 200
In this article, I gave many examples in VB.NET than on C#. Several of them can be written in C# syntax also. If you have any doubts, please post in the discussion area.
We will go further in-depth in my upcoming articles. I hope you enjoyed the article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
