Grouping and Aggregating When Querying LINQ to SQL

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();

Querying with LINQ “COUNT” in different contexts

One of the most frequently used functions is COUNT. It comes in different flavors. The following three have different meanings:

SELECT COUNT(*) FROM Orders

SELECT COUNT(ShipRegion) FROM Orders

SELECT COUNT(DISTINCT ShipRegion) FROM Orders

The first returns the total number of rows available in the Orders table. The second returns the number of non-null values in the “ShipRegion” column of the Orders table. The third returns the number of values in the “ShipRegion” column without counting the repeated and null values.

From the point of view of LINQ, it always counts objects (and not values) in the collection. So, both of the following would return the same count values (i.e., total number of objects in Orders).

Dim q = (From p In db.Orders _

Select p).Count

Dim q = (From p In db.Orders _

Select p.ShipRegion).Count

In both of the above cases, the SQL SELECT which gets generated from the above LINQ would be same (with COUNT(*)). There exists no direct way in LINQ to achieve the above type of SQL SELECT query. But we can do it in an indirect manner.

I would like to rewrite the following query:

SELECT COUNT(ShipRegion) FROM Orders

in the following manner:

SELECT COUNT(*) FROM Orders WHERE ShipRegion IS NOT NULL

The same can be represented in LINQ (VB.NET) as follows:

Dim q = (From p In db.Orders _

Where p.ShipRegion IsNot Nothing _

Select p.ShipRegion).Count

You can observe that I am explicitly filtering nulls in the above LINQ query. The same can be achieved in C# LINQ as follows:

var q = (from p in db.Orders

where p.ShipRegion != null 

select p.ShipRegion).Count();

And now, the following SQL SELECT:

SELECT COUNT(DISTINCT ShipRegion) FROM Orders

can be written in VB.NET LINQ as follows:

Dim q = (From p In db.Orders _

Where p.ShipRegion IsNot Nothing _

Select p.ShipRegion).Distinct.Count

And of course, in C# LINQ as follows:

var q = (from p in db.Orders

where p.ShipRegion != null

select p.ShipRegion).Distinct().Count();

Note that during the execution of above LINQ queries, the SQL SELECT which gets generated would be entirely different from what I have posted above. But, finally, the result would be same.

Aggregations in LINQ (like GROUP BY of SQL in LINQ)

In all of the above sections, we simply worked with group (or aggregation) functions. We didn’t really aggregate data based on certain criteria.

Say I would like to have a report of “Customer wise number of orders.” To achieve this, the following is the SQL SELECT:

SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID

The above SELECT with GROUP BY can be written in LINQ (VB.NET) as follows:

Dim q = From p In db.Orders _

Group p By p.CustomerID _

Into Count() _

Select _

CustomerID, _

NoOfOrders = Count

Me.GridView1.DataSource = q

Me.GridView1.DataBind()

In C# LINQ, it would be as follows:

var q = from p in db.Orders

group p by p.CustomerID

into g

select new { CustomerID = g.Key, NoOfOrders = g.Count() };

this.GridView1.DataSource = q;

this.GridView1.DataBind();

In VB.NET LINQ, we have the flexibility to write the same query in different ways as follows:

Dim q = From p In db.Orders _

Group p By p.CustomerID _

Into Group _

Select CustomerID, NoOfOrders = Group.Count

Dim q = From p In db.Orders _

Group By p.CustomerID _

Into OrderCount = Count()

I prefer the last (for VB.NET), as it is quite simple and clear.

Aggregations in LINQ (like GROUP BY of SQL in LINQ): continued

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)

Aggregations in LINQ (like GROUP BY..HAVING of SQL in LINQ)

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