Grouping and Aggregating When Querying LINQ to SQL
(Page 1 of 5 )
This is the second article in a series focusing on querying using “LINQ to SQL.” It covers grouping and aggregating data with both SQL and respective LINQ examples in VB.NET and C#.
If you are a bit new to programming LINQ, please consider reading my previous articles titled “Beginning ‘LINQ to SQL’ using Visual Studio 2008” and “Introducing ‘LINQ to SQL’ designer using Visual Studio 2008.” If you are new to basic LINQ operators, refer to my first article in this series, "Querying LINQ to SQL: Basics."
For the convenience of this article, I created a LINQ model using the “LINQ to SQL designer” as follows:
All of the queries in this article will focus on the above model.
The entire source code for this article is available in the form of a free downloadable zip file. The solution was developed using Microsoft Visual Studio 2008 Team Edition on Microsoft Windows Server 2003 Standard Edition with Microsoft SQL Server 2005 Developer Edition. I didn’t really test it in any other environment. I request that you post in the discussion area if you have any problems in execution.
Working with Aggregation functions (or group functions)
SQL has several group functions (aggregate functions) to operate on a set of values. MAX, MIN, AVG, COUNT are a few of them. Now, we will try to write LINQ queries based on those functions.
The following SQL SELECT statement:
SELECT MAX(Freight) FROM Orders
Can be written using VB.NET LINQ as follows:
Dim q = (From p In db.Orders _
Select p.Freight).Max
Me.lblResult.Text = q
And in C# LINQ as follows:
var q = (from p in db.Orders
select p.Freight).Max();
this.lblResult.Text = q.ToString();
Similarly, all of the following (in VB.NET LINQ) are valid:
Dim q = (From p In db.Orders _
Select p.Freight).Sum
Dim q = (From p In db.Orders _
Select p.Freight).Min
Dim q = (From p In db.Orders _
Select p).Count
Dim q = (From p In db.Orders _
Select p.Freight).Average
The above in C# LINQ would be as follows:
var q = (from p in db.Orders
select p.Freight).Sum();
var q = (from p in db.Orders
select p.Freight).Min();
var q = (from p in db.Orders
select p).Count();
var q = (from p in db.Orders
select p.Freight).Average();
Next: Querying with LINQ “COUNT” in different contexts >>
More .NET Articles
More By Jagadish Chaterjee
