对 IQueryable 直接调用聚合函数

聚合函数（Count、Sum、Average、Max、Min）可以直接在 IQueryable 对象上调用，不需要先 ToList()。调用时 LINQ to SQL 会生成对应的聚合 SQL，在数据库端执行，只返回单个值。

---

示例代码

var q = from s in db.Student
        select s;

var count = q.Count();                    // 总记录数
var totalAge = q.Sum(m => m.age);         // 年龄总和
var avgAge = q.Average(m => m.age);       // 平均年龄
var maxAge = q.Max(m => m.age);           // 最大年龄
var minAge = q.Min(m => m.age);           // 最小年龄
var msg = string.Join(",", q.Select(m => m.name + ":" + m.age)); // 拼接（内存执行）
var list = q.ToList();                    // 查全部记录Code language: JavaScript (javascript)

---

生成的 SQL

每次聚合调用各自生成一条 SQL，全部在数据库端执行：

-- Count()
SELECT COUNT(*) AS [value] FROM [dbo].[Student] AS [t0]

-- Sum()
SELECT SUM([t0].[age]) AS [value] FROM [dbo].[Student] AS [t0]

-- Average()
SELECT AVG([t0].[age]) AS [value] FROM [dbo].[Student] AS [t0]

-- Max()
SELECT MAX([t0].[age]) AS [value] FROM [dbo].[Student] AS [t0]

-- Min()
SELECT MIN([t0].[age]) AS [value] FROM [dbo].[Student] AS [t0]Code language: CSS (css)

注意：​ 每次调用聚合方法都会单独发一条 SQL​ 到数据库。上面 5 个聚合 = 5 次查询。

---

带条件的聚合

// 只统计 age > 18 的记录
var count = q.Count(m => m.age > 18);
var sum = q.Where(m => m.age > 18).Sum(m => m.age);Code language: JavaScript (javascript)

生成 SQL：

SELECT COUNT(*) AS [value] FROM [dbo].[Student] AS [t0] WHERE [t0].[age] > 18
SELECT SUM([t0].[age]) AS [value] FROM [dbo].[Student] AS [t0] WHERE [t0].[age] > 18Code language: CSS (css)

---

与分组聚合的区别

场景	写法	生成 SQL
整体聚合	q.Sum(m => m.age)	SELECT SUM(age) FROM Student
分组聚合	group s by s.sex into g select new { g.Key, sum = g.Sum(m => m.age) }	SELECT sex, SUM(age) FROM Student GROUP BY sex

---

string.Join 的执行位置

var msg = string.Join(",", q.Select(m => m.name + ":" + m.age));Code language: JavaScript (javascript)

这一步无法翻译为 SQL，会先把所有记录加载到内存，再在客户端拼接。等效于：

var list = q.ToList(); // 先查全部
var msg = string.Join(",", list.Select(m => m.name + ":" + m.age)); // 再拼接Code language: PHP (php)

---

多次聚合的性能注意

如果需要对同一数据集做多个聚合，每次调用都会发一条 SQL。数据量大时建议：

方案：一次查询，内存中聚合

var list = q.ToList(); // 一次查库
var count = list.Count;
var totalAge = list.Sum(m => m.age);
var avgAge = list.Average(m => m.age);Code language: PHP (php)

方案：用 group by 一次聚合多个值

var stats = (from s in db.Student
             group s by 1 into g
             select new
             {
                 count = g.Count(),
                 totalAge = g.Sum(m => m.age),
                 avgAge = g.Average(m => m.age),
                 maxAge = g.Max(m => m.age),
                 minAge = g.Min(m => m.age)
             }).First();Code language: JavaScript (javascript)

生成单条 SQL：

SELECT COUNT(*) AS [count], SUM([t0].[age]) AS [totalAge], 
       AVG([t0].[age]) AS [avgAge], MAX([t0].[age]) AS [maxAge], 
       MIN([t0].[age]) AS [minAge]
FROM [dbo].[Student] AS [t0]Code language: CSS (css)

推荐：​ 需要多个聚合值时，用 group by 常量 一次查出，只发一条 SQL。