Apr
16th | 2008

How to use Aggregate functions with LINQ to SQL

Filed under C#, SQL | Posted by Shahar A

LINQ to SQL maps the relational database into an object model, and when the program runs the queries in the object model are translated into SQL. SQL contains Aggregate functions like SUM, AVG, MIN, MAX and more. Lets see how this functionality can be implemented using LINQ Enumerable standard aggregation operators. You can also create custom Aggregations to implement any other required behavior. The Examples below show how use Average, Count and Sum using the following SQL table:

table 

Average

var query1 = from income in data.Incomes
             group income by income.name into result
             select new
             {
                 Name = result.Key,
                 Average = result.Average(i => i.income)
             };  

foreach (var q in query1)
{
    Debug.WriteLine(string.Format(“Name: {0} , Average income: {1}”, q.Name, q.Average));
}

Sum

var query2 = from income in data.Incomes
             group income by income.name into result
             select new
             {
                 Name = result.Key,
                 Sum = result.Sum(i => i.income)
             };  

foreach (var q in query2)
{
    Debug.WriteLine(string.Format(“Name: {0} , Sum income: {1}”, q.Name, q.Sum));
}

Count

var query3 = from income in data.Incomes
             group income by income.name into result
             select new
             {
                 Name = result.Key,
                 Count = result.Count()
             };  

foreach (var q in query3)
{
    Debug.WriteLine(string.Format(“Name: {0} , Count: {1}”, q.Name, q.Count));
}

Using the DebuggerWriter class by Kris Vandermotten this is the generated SQL:

SELECT AVG([t0].[Income]) AS [Average], [t0].[Name]
FROM [dbo].[Incomes] AS [t0]
GROUP BY [t0].[Name]
…
SELECT SUM([t0].[Income]) AS [Sum], [t0].[Name]
FROM [dbo].[Incomes] AS [t0]
GROUP BY [t0].[Name]
…
SELECT COUNT(*) AS [Count], [t0].[Name]
FROM [dbo].[Incomes] AS [t0]
GROUP BY [t0].[Name]

For more information on LINQ to SQL see this excellent tutorial

Tags: , , , , , , ,

  1. 2 Trackback(s)

  2. Apr 16, 2008: How to use Aggregate functions with LINQ to SQL « Rams On It - .NET
  3. May 1, 2008: Aggregate Functions in LINQ - Jason N. Gaylord's Blog

Post a Comment

Search Dev102