C# LINQ深入解析：从入门到性能优化

// 查询语法（Query Syntax）
var result1 = from u in users
              where u.Age > 18
              orderby u.Name
              select new { u.Name, u.Email };

// 方法语法（Method Syntax / Fluent API）
var result2 = users
    .Where(u => u.Age > 18)
    .OrderBy(u => u.Name)
    .Select(u => new { u.Name, u.Email });

var orderDetails = from o in orders
                   join p in products on o.ProductId equals p.Id
                   group o by p.Category into g
                   select new {
                       Category = g.Key,
                       TotalAmount = g.Sum(o => o.Amount)
                   };

var query = users.Where(u => u.IsActive); // 此时没有执行任何查询

Console.WriteLine("查询还没开始");

foreach (var user in query) // 迭代时才真正执行
{
    Console.WriteLine(user.Name);
}

var activeUsers = users.Where(u => u.IsActive);

int count = activeUsers.Count();    // 第一次遍历
var list  = activeUsers.ToList();   // 第二次遍历 — 重复计算！

// 正确做法：物化一次
var activeUsersList = users.Where(u => u.IsActive).ToList();
int count2 = activeUsersList.Count;
var list2  = activeUsersList;

var queries = new List<IEnumerable<User>>();
for (int i = 0; i < 3; i++)
{
    // 错误：所有 query 都捕获了同一个 i，最终都是 i=3
    queries.Add(users.Where(u => u.DeptId == i));
}

// 正确：在循环内创建局部变量
for (int i = 0; i < 3; i++)
{
    int deptId = i;
    queries.Add(users.Where(u => u.DeptId == deptId));
}

var stats = users.Where(u => u.IsActive)
                 .GroupBy(u => u.Department)
                 .Select(g => new {
                     Department = g.Key,
                     Count    = g.Count(),
                     AvgAge   = g.Average(u => u.Age),
                     MaxSalary = g.Max(u => u.Salary)
                 });

var list1 = new[] { 1, 2, 3, 4, 5 };
var list2 = new[] { 3, 4, 5, 6, 7 };

var union     = list1.Union(list2);      // { 1,2,3,4,5,6,7 }
var intersect = list1.Intersect(list2);  // { 3,4,5 }
var except    = list1.Except(list2);     // { 1,2 }

int page = 2, pageSize = 10;
var paged = users.OrderBy(u => u.Id)
                 .Skip((page - 1) * pageSize)
                 .Take(pageSize)
                 .ToList();

// 每个 Department 有多个 User，获取所有 User
var allUsers = departments.SelectMany(d => d.Users);

// 带父对象
var userDeptPairs = departments
    .SelectMany(d => d.Users, (dept, user) => new { dept.Name, user });

// LINQ to Objects — 先把所有数据加载到内存再过滤（危险！）
var result = dbContext.Users.ToList().Where(u => u.Age > 18);

// LINQ to SQL — 生成 WHERE age > 18 的 SQL，只取需要的数据
var result = dbContext.Users.Where(u => u.Age > 18).ToList();

// 错误：每个订单都触发一次 SQL 查询用户信息
var orders = dbContext.Orders.ToList();
foreach (var order in orders)
{
    Console.WriteLine(order.User.Name); // N 次查询
}

// 正确：用 Include 预加载
var orders = dbContext.Orders.Include(o => o.User).ToList();

// 低效：需要遍历所有元素计数
if (users.Count() > 0) { }

// 高效：找到第一个就返回
if (users.Any()) { }

// 同等效果，但语义更清晰
var user = users.FirstOrDefault(u => u.Id == id);
// 等价于
var user = users.Where(u => u.Id == id).FirstOrDefault();