Using LINQ to Aggregate and Group

Recently when I was presenting at Tech Days Canada I was preparing some examples of how LINQ could be used to do in-memory manipulations of data to help avoid repeated database calls. In the presentation, I showed some quick code samples, and I thought that I would follow this up with a few simple examples here in a blog post. Before I start I wanted to share that the code shown here is actual production code that is being used to run the reporting on MyWebKeepAlive, the keep-alive service offered by my company IowaComputerGurus.

A Bit of Background

Before getting into the LINQ specific examples I will provide a bit of information regarding the object that we will be using LINQ against. The name of the object is "CheckCompleteInfo" and it is defined by the class as listed below. Each instance of this class represents a completed check of a URL and contains information regarding the date, duration, response code, and content length. We will be working with an initial dataset that contains all checks for a specific URL as taken over a 7 day period.

public class CheckCompleteInfo : IHydratable 
{
    public int UrlId { get; set;}
    public DateTime CompleteDate { get; set;}
    public int Duration { get; set;}
    public string ResponseCode { get; set;}
    public int ContentLength { get; set; }

    //Interface declaration removed for brevity
}

The Business Requirement

With a bit of information regarding the specifics of the code that we will be working with we can now share the business requirements of what we need from the data. The report page is to display three pieces of information. The first is a line chart showing the duration of all checks for the 7 day period, no LINQ is needed, and the specifics of this implementation will not be demonstrated. The second piece of data is the Average duration of all checks in the last 7 days, and the final piece of information is a chart that shows each response code that was returned and the number of times it happened.

Since the first data requirement will pull all records for display, we can simply use the List that is used to then calculate the other data elements, the following two sections will show us how this is accomplished.

Getting an Average Using LINQ

To accomplish the second business requirement we need to get the average value from the "Duration" property of each item in the collection. If you were going to do this outside of LINQ you might think of using a loop or some other means to add up the values then divide by the count. However, with LINQ we can do this quickly, and without any of our own loops. Below is the code needed to query and display the results, I will explain the code afterward.

var query = from durations in oHistory 
                select durations.Duration;
decimal averageDuration = Math.Round(query.Average(), 1)
lblAverageResponse.Text = averageDuration.ToString();

As you can see from this code it is very simple to perform the aggregation. In the case of this example, the first statement uses a LINQ query to get a list of all durations, you can look at this as a collection of Integers if you would like. The oHistory variable is the List that contains all element. The second statement then calls the "Average" extension method to average the values in the list and Math.Round is used to round items to a single decimal point. With this, I have a result that I can display where needed and it was not necessary to write any of my own complex methods or another stored procedure to query the results.

Grouping Data Using LINQ

The final business requirement is a bit more complex, in the end, I need an object that has two data elements for each record, ResponseCode, and Count. TO do this the LINQ is a bit more complex, but still fairly straightforward, below is the code necessary, with an explanation below. As with the last example, oHistory is the collection of objects we are working with.

var responseCodes = from c in oHistory
                    group c by c.ResponseCode
                        into result
                        select new { ResponseCode = result.Key, Count = result.Count() };
chtUrlStatus.DataSource = responseCodes.ToArray();
chtUrlStatus.DataBind();

As you can see here we are able to do this with one LINQ statement, then call set the data source for our chart. The LINQ here is easy to look at in 4 parts, starting with the first line we define what we are working with. The second statement defines that we want to group items by ResponseCode. The third line tells LINQ that we want to select the grouping into a variable called "result". The final line defines that we want to select a NEW object and that the object should have two properties. The first called ResponseCode which should be equal to the key of the result. Key represents the value that was grouped on. The second property should be called "Count" and should have the value of result.count() which counts the occurrences of the specific key.

In the end, the "responseCodes" variable now have an object with two properties and can be bound to our chart following standard data binding techniques.

Summary

I hope this article was able to illustrate some of the powers of using LINQ within your applications to be able to make full use of data that has already been retrieved. LINQ is not appropriate for all solutions but is a very powerful tool if you take the time to understand its usage. As always feel free to share your feedback below. If you need assistance with LINQ feel free to post to the forum on this site.