Grouping and Counting in LINQ to SQL

Introduction
In this earlier blog, I covered the basic steps leading up to creating grouped data and displaying it.  Now I want to add totals to those groups.

 Basic Grouping
The final code sample of the previous blog was :

Dim NWOrderContext As New NorthwindOrdersDataContext

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'  Group by cities
Dim NWOrders = From orders In NWOrderContext.Orders
Where orders.Freight > 99 And orders.ShipCountry = "USA"
Group orders By orders.ShipCity Into Cities = Group
Select Cities, ShipCity

  For Each Order In NWOrders
Console.WriteLine(String.Format("{0}", Order.ShipCity))

    For Each city In Order.Cities
Console.WriteLine(String.Format("{0}{1} ", Space(40), FormatCurrency(city.Freight)))
Next
Next
End Sub

And the resulting output was:

Using the Count Aggregate function
It’s quite a small step to include the total count of invoices per city into the query. Take a look at the amended code below:

Dim NWOrders = From orders In NWOrderContext.Orders
Where orders.Freight > 99 And orders.ShipCountry = "USA"
Group orders By orders.ShipCity Into CityCount = Count(), Cities = Group
Select Cities, ShipCity, CityCount

As you can see from the bold type, I’ve used the Count function and stored the result of the count in a variable named CityCount.   Because the Group By clause stipulates that the ShipCity property is used for the grouping action, the Count function will count the number orders that exist for each ShipCity.

When I run this query and display the result:

For Each Order In NWOrders
Console.WriteLine(String.Format("{0} ({1}) ", Order.ShipCity, Order.CityCount))

  For Each city In Order.Cities
Console.WriteLine(String.Format("{0}{1}", Space(55), FormatCurrency(city.Freight)))

  Next
Next

the output will be:

A more realistic scenario
Now, in real world usage the data displayed above isn’t likely to be much use, but my aim here is just to demo these LINQ techniques. But let’s at least make the data a bit more useful.

First, we can get the order dates and include them in the group:

Dim NWOrders = From orders In NWOrderContext.Orders
Where orders.Freight > 99 And orders.ShipCountry = "USA"
Group orders.OrderDate, orders.Freight By orders.ShipCity Into CityCount = Count(), Cities = Group
Select Cities, ShipCity, CityCount

Then, enumerating through the collections of anonymous types:

For Each SingleOrder In NWOrders
Console.WriteLine(String.Format("{0} ({1}) {2}", SingleOrder.ShipCity, SingleOrder.CityCount, Space(5)))

  For Each city In SingleOrder.Cities
Console.WriteLine(String.Format("{0}{1}  ordered on  {2} ", Space(30), FormatCurrency(city.Freight),
FormatDateTime(CDate(city.OrderDate), DateFormat.ShortDate)))

  Next
Next

the additional data is displayed:

If you’ve looked at the code closely, you’ll see that I’ve changed the name I’m using for the anonymous type in the For Each code block.  I’ve done this, because I think it’s easy to become confused by all the various uses of ‘order’ and ‘Order’, and it occurred to me that some people might think that when they see ‘Order’ in that kind of code that they are dealing with an order object created directly from the database or the object model.  So by changing the name to something completely different, I hope it makes it clearer that SingleOrder is simply a temporary anonymous type created just for this process.

Including Order By in the Query
The orders are listed in date order above because that’s the way they’re ordered in the database.  The LINQ query I used previously can be tweaked to change this.  Let’s say we want to list those freight costs by descending value:

Dim NWOrders = From orders In NWOrderContext.Orders
Where orders.Freight > 99 And orders.ShipCountry = "USA"
Order By orders.Freight Descending
            Group orders.OrderDate, orders.Freight By orders.ShipCity Into CityCount = Count(), Cities = Group
Select Cities, ShipCity, CityCount

When I run the same display code, the result looks like this:

   

Accessing multiple tables
The final technique I want to cover here is another common requirement.  All the data you can see in the screenshot above comes from the Order table.

What happens if, for example, you needed to include the CompanyName field from the Customer table in the display?  You’ll be pleased to hear that it’s surprisingly easy, and that’s because the object context understands the relationships between the tables and allows you simply to drill through one table and into another table with which it has a relationship.

So, because the Customer table has a one-to-many relationship with the Order table, it’s possible to navigate from any particular order and grab it’s related CompanyName from the Customer table.  Here’s the syntax that does the trick:

Dim NWOrders = From orders In NWOrderContext.Orders
Where orders.Freight > 99 And orders.ShipCountry = "USA"
Group orders.OrderID, orders.OrderDate, orders.Freight, orders.Customer.CompanyName By orders.ShipCity Into CityCount = Count(), Cities = Group
Select Cities, ShipCity, CityCount, CompanyName

And then to display the results:

For Each SingleOrder In NWOrders
Console.WriteLine(String.Format("{0} ({1}) {2}", SingleOrder.ShipCity, SingleOrder.CityCount, Space(5)))

  For Each city In SingleOrder.Cities
Console.WriteLine(String.Format("{0}{1}  ordered on  {2} by {3}", Space(30), FormatCurrency(city.Freight),
FormatDateTime(CDate(city.OrderDate), DateFormat.ShortDate),
city.CompanyName))
Next
Next

The output of this will be:

(You’ll see that I didn’t include the Order By clause in this sample, but it’s no problem to include it if needed).

Summary
I’ve covered the basics of grouping and using the Count aggregate function in these two blogs.  There are several more aggregate functions that are worth knowing about, and I’ll deal with them in a future blog.

LINQ is one of those strange topics that looks as though everything is straightforward, but then every so often it’ll simply not do what you think it should.  In almost all cases, in my experience, it isn’t because LINQ can’t do it – it’s usually that you haven’t asked it in the right way.  I find that if I hit a wall, the best first step is to strip out every non-critical element from the query and then rebuild it an element at a time until I find what I’ve missed or misunderstood.  More than anything, practising and experimenting are the keys to success in LINQ.

Posted Mar 30 2011, 09:55 PM by Ged Mead Filed under: .NET, VB.NET, Visual Basic.NET, Grouping, Count function

View the original article here