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
Add a comment
|
3 Answers
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
Comments
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)
}
Comments
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)
};
2 Comments
Jeff Langston
Want that produce 5 separate queries?
argh
You can't do it otherwise in linq, those methods are 'terminators'