CODEWITHSUNDEEP
c#linq-to-sql
Jeff Langston
Jeff Langston

Reputation: 83

Linq-to-SQL statement for multiple aggregate values in single expression

How would I write a Linq-to-SQL statement for the following TSQL?

select 
  count(*),
  sum(Amount),
  avg(Amount),
  min(Amount),
  max(Amount) 
from 
  TableName

Upvotes: 7

Views: 1774

Answers (3)

alex.cedeno
alex.cedeno

Reputation: 41

HACK ALERT, but it works. Try to group your records by a condition all of them share:

var result = from g in db.Employees
        group g by g.Id.GetType() == typeof(int) into gg
        select new 
        {
            Count = gg.Count(),
            Sum = gg.Sum(x => x.Salary)
        };

This generates the SQL:

SELECT COUNT(*) AS [Count], SUM([t1].[Salary]) AS [Sum]
FROM (
SELECT 1 AS [value], [t0].[Salary]
FROM [dbo].[Employee] AS [t0]
) AS [t1]
GROUP BY [t1].[value]

There is a subquery involved, but hey! it's only one db trip

Upvotes: 4

Kelsey
Kelsey

Reputation: 47726

You could do:

var result = new
{
    Count = db.TableName.Count(),
    Sum = db.TableName.Sum(r => r.Amount),
    Average = db.TableName.Avg(r => r.Amount),
    Min = sb.TableName.Min(r => r.Amount),
    Max = db.TableName.Max(r => r.Amount)        
}

Upvotes: 2

Stephan
Stephan

Reputation: 5488

It's probably easier to pull the values individually but you could do with an anonymous type.

var aggregates = new {
Count = context.TableName.Count(),
Sum = context.TableName.Sum(t => t.Amount),
Avg = context.TableName.Avg(t => t.Amount),
Min = context.TableName.Min(t => t.Amount),
Max = context.TableName.Max(t => t.Amount)
};

Upvotes: 1

Related Questions