Advertise on Dev102 Advertise on Dev102 Advertise on Dev102 Advertise on Dev102
Write Article for Dev102

Write for Dev102!

We pay for user submitted tutorials and articles that we publish. Anyone can send in a contribution

Learn More

How to use Aggregate functions with LINQ to SQL

By Shahar A

April 16, 2008

C#, SQL

14 Comments

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

Related Articles

  1. Very Good Linq Editor
  2. LINQ to XML in 3 easy steps
  3. How to reset Identity Increment value in MS SQL
  4. How to Search the File System Using LINQ Queries
  5. Call Virtual Functions From Constructors?

Tags :

14 Responses to “How to use Aggregate functions with LINQ to SQL”


  1. idollaheeva

    Said on August 2, 2008 :

    Tahnks for posting

  2. Ania

    Said on January 1, 2009 :

    Average = result.Average(i => i.income)
    …What’s result?

  3. Ania

    Said on January 1, 2009 :

    ah..okay..I didn’t see that “into” :D

  4. Sergius

    Said on January 7, 2009 :

    {
    useful post. Thanks
    }

  5. Vinay

    Said on January 28, 2009 :

    A novice in trouble …
    I tried using the Average function given above. I have a text file from which I have read the data. Now I am trying to get the average using the code below
    var marksBySubject = from m in marks
    group m by m.subjectName into results
    select new
    {
    subjectName = results.Key,
    avgMarks = results.Average(i => i.marksObtained )
    };

    I get an error “cannot implicitly convert type “string” to “decimal?” on the line where lambda expression is used. I am unable to figure out what is the solution. Help is much appreciated.
    Thanks

  6. manoj

    Said on April 20, 2009 :

    Thank you very much..

  7. SQL-Developer

    Said on April 2, 2010 :

    Hello,
    LINQ is great for developers who does not deal with sql database access, querying, etc.
    But the syntax of LINQ seems to be complex and I guess must be improved.
    But the above samples are great value for Linq learners.

  8. pratik

    Said on August 31, 2011 :

    niec1..
    all at one place..
    good..

  9. Water Damage Restoration

    Said on October 11, 2011 :

    What is the difference between linq and lamda? Great article.

  10. Guillaume

    Said on November 4, 2011 :

    Sweet stuff finally got it after wasting 1h of my life in 101 linq examples on MSDN.

    It look like you have php errors meanwhile

    Warning: preg_match() [function.preg-match]: Compilation failed: nothing to repeat at offset 1 in /home/razamit/public_html/Dev102/wp-content/plugins/wassup/wassup.php on line 3954

    Warning: preg_match() [function.preg-match]: Compilation failed: nothing to repeat at offset 1 in /home/razamit/public_html/Dev102/wp-content/plugins/wassup/wassup.php on line 3954

  11. Shyam

    Said on November 29, 2011 :

    Thanks for this wonderful post

3 Trackback(s)

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

Post a Comment