CLINQ数据查询与优化Skill csharp-linq

---

name: csharp-linq user-invocable: false description: 当使用LINQ查询和方法语法、延迟执行和性能优化时使用。在C#中查询集合时使用。 allowed-tools:

• Bash
• Read
• Write
• Edit

---

C# LINQ

掌握语言集成查询（LINQ）用于在C#中查询和转换数据。 此技能涵盖查询语法、方法语法、延迟执行、性能优化以及C# 8-12中的高级LINQ模式。

LINQ查询语法与方法语法

LINQ支持两种语法：查询语法（类似SQL）和方法语法（流畅式）。 两者编译为相同的代码。

查询语法

var students = new List<Student>
{
    new Student { Name = "Alice", Grade = 85, Age = 20 },
    new Student { Name = "Bob", Grade = 92, Age = 21 },
    new Student { Name = "Charlie", Grade = 78, Age = 20 }
};

// 查询语法 - 类似SQL
var topStudents = from student in students
                  where student.Grade >= 80
                  orderby student.Grade descending
                  select new { student.Name, student.Grade };

foreach (var student in topStudents)
{
    Console.WriteLine($"{student.Name}: {student.Grade}");
}

方法语法

// 方法语法 - 流畅API
var topStudents = students
    .Where(s => s.Grade >= 80)
    .OrderByDescending(s => s.Grade)
    .Select(s => new { s.Name, s.Grade });

// 方法语法更灵活，适用于复杂查询
var result = students
    .Where(s => s.Age >= 20)
    .GroupBy(s => s.Age)
    .Select(g => new
    {
        Age = g.Key,
        AverageGrade = g.Average(s => s.Grade),
        Count = g.Count()
    })
    .OrderBy(x => x.Age);

何时使用每种语法

// 查询语法更适合连接
var query1 = from student in students
             join course in courses on student.Id equals course.StudentId
             where course.Grade > 80
             select new { student.Name, course.Title };

// 方法语法更适合链式调用和复杂逻辑
var query2 = students
    .Where(s => s.Age >= 20)
    .SelectMany(s => s.Courses)
    .Where(c => c.Grade > 80)
    .Distinct()
    .Take(10);

// 混合方法
var query3 = (from s in students
              where s.Age >= 20
              select s)
    .Take(10)
    .ToList(); // 强制执行

延迟执行

LINQ查询使用延迟执行 - 直到枚举时才执行。

理解延迟执行

var numbers = new List<int> { 1, 2, 3, 4, 5 };

// 查询已定义但未执行
var query = numbers.Where(n => n > 2);

// 添加更多数字
numbers.Add(6);
numbers.Add(7);

// 查询在枚举时执行
foreach (var num in query) // 获取：3, 4, 5, 6, 7
{
    Console.WriteLine(num);
}

// 查询再次执行（看到当前状态）
var count = query.Count(); // 5

// 使用ToList()、ToArray()等强制立即执行
var snapshot = numbers.Where(n => n > 2).ToList();
numbers.Add(8);
Console.WriteLine(snapshot.Count); // 仍为5，不是6

延迟执行与立即执行

public class DeferredExecutionExample
{
    public void Demonstrate()
    {
        var data = new List<int> { 1, 2, 3, 4, 5 };

        // 延迟 - 查询尚未执行
        var deferred = data.Where(x => x > 2);

        // 立即 - 查询现在执行
        var immediate = data.Where(x => x > 2).ToList();

        // 修改源数据
        data.Add(6);

        Console.WriteLine(deferred.Count());  // 4（包括6）
        Console.WriteLine(immediate.Count()); // 3（添加6前的快照）
    }

    // 危险模式：每次迭代重建查询
    public void DangerousPattern()
    {
        var data = GetData(); // 昂贵操作

        // ❌ 错误 - GetData()多次调用
        foreach (var item in GetData().Where(x => x.IsActive))
        {
            Process(item);
        }

        // ✅ 正确 - GetData()调用一次
        var items = GetData().Where(x => x.IsActive).ToList();
        foreach (var item in items)
        {
            Process(item);
        }
    }
}

IEnumerable与IQueryable

IEnumerable在内存中执行（LINQ to Objects）。IQueryable翻译为表达式树以进行远程执行（LINQ to SQL、EF）。

IEnumerable<T> - 内存中

public class InMemoryQueries
{
    public void QueryInMemory()
    {
        var products = new List<Product>
        {
            new Product { Id = 1, Name = "Laptop", Price = 999 },
            new Product { Id = 2, Name = "Mouse", Price = 25 },
            new Product { Id = 3, Name = "Keyboard", Price = 75 }
        };

        // IEnumerable - 在内存中执行
        IEnumerable<Product> query = products
            .Where(p => p.Price > 50)
            .OrderBy(p => p.Name);

        // 所有过滤在C#代码中发生
        foreach (var product in query)
        {
            Console.WriteLine($"{product.Name}: ${product.Price}");
        }
    }
}

IQueryable<T> - 表达式树

public class QueryableExamples
{
    private readonly DbContext _context;

    // IQueryable - 翻译为SQL
    public async Task<List<Product>> GetExpensiveProductsAsync()
    {
        // 查询构建表达式树
        IQueryable<Product> query = _context.Products
            .Where(p => p.Price > 50)
            .OrderBy(p => p.Name);

        // 在此生成并执行SQL
        return await query.ToListAsync();
        // SQL：SELECT * FROM Products WHERE Price > 50 ORDER BY Name
    }

    // 可组合查询
    public IQueryable<Product> GetActiveProducts()
    {
        return _context.Products.Where(p => p.IsActive);
    }

    public async Task<List<Product>> GetExpensiveActiveProductsAsync()
    {
        // 组合查询 - 仍生成单个SQL
        var products = await GetActiveProducts()
            .Where(p => p.Price > 100)
            .ToListAsync();

        // SQL：SELECT * FROM Products WHERE IsActive = 1 AND Price > 100
        return products;
    }
}

混合IEnumerable和IQueryable

public class MixingQueries
{
    private readonly DbContext _context;

    public async Task<List<ProductDto>> GetProductsDangerousAsync()
    {
        // ❌ 错误 - ToList()首先将所有产品带入内存
        var products = await _context.Products.ToListAsync();

        // 然后在内存中过滤（低效）
        return products
            .Where(p => p.Price > 100) // 在内存中
            .Select(p => new ProductDto { Name = p.Name })
            .ToList();
    }

    public async Task<List<ProductDto>> GetProductsEfficientAsync()
    {
        // ✅ 正确 - 全部在SQL中
        return await _context.Products
            .Where(p => p.Price > 100) // 在SQL中
            .Select(p => new ProductDto { Name = p.Name }) // 在SQL中
            .ToListAsync(); // 执行一次
    }

    public async Task<List<Product>> ComplexFilterAsync()
    {
        // ✅ 正确 - 尽可能在SQL中，必要时在内存中
        return await _context.Products
            .Where(p => p.Price > 50) // SQL
            .ToListAsync() // 执行SQL
            .ContinueWith(t => t.Result
                .Where(p => ComplexInMemoryCheck(p)) // C#谓词
                .ToList()
            );
    }

    private bool ComplexInMemoryCheck(Product product)
    {
        // 无法翻译为SQL的逻辑
        return product.Name.Split(' ').Length > 2;
    }
}

常见LINQ操作符

Where - 过滤

var numbers = Enumerable.Range(1, 100);

// 简单过滤
var evens = numbers.Where(n => n % 2 == 0);

// 多条件
var filtered = numbers.Where(n => n > 10 && n < 50 && n % 3 == 0);

// 带索引过滤
var everyThird = numbers.Where((n, index) => index % 3 == 0);

// 复杂过滤
var products = GetProducts()
    .Where(p => p.IsActive)
    .Where(p => p.Price >= 10 && p.Price <= 100)
    .Where(p => p.Category.StartsWith("Electronics"));

Select - 投影

var students = GetStudents();

// 简单投影
var names = students.Select(s => s.Name);

// 匿名类型
var summary = students.Select(s => new
{
    s.Name,
    s.Grade,
    Status = s.Grade >= 80 ? "Pass" : "Fail"
});

// 带索引投影
var indexed = students.Select((s, i) => new
{
    Index = i,
    Student = s
});

// DTOs
var dtos = students.Select(s => new StudentDto
{
    FullName = $"{s.FirstName} {s.LastName}",
    GradePoint = s.Grade / 100.0
});

SelectMany - 扁平化

var departments = new List<Department>
{
    new Department
    {
        Name = "IT",
        Employees = new[]
        {
            new Employee { Name = "Alice" },
            new Employee { Name = "Bob" }
        }
    },
    new Department
    {
        Name = "HR",
        Employees = new[]
        {
            new Employee { Name = "Charlie" }
        }
    }
};

// 扁平化嵌套集合
var allEmployees = departments.SelectMany(d => d.Employees);

// 带结果选择器
var employeesWithDept = departments.SelectMany(
    dept => dept.Employees,
    (dept, emp) => new { Department = dept.Name, Employee = emp.Name }
);

// 多层级
var orders = GetOrders();
var allItems = orders
    .SelectMany(o => o.OrderLines)
    .SelectMany(ol => ol.Items);

GroupBy - 分组

var sales = GetSales();

// 简单分组
var byCategory = sales.GroupBy(s => s.Category);

foreach (var group in byCategory)
{
    Console.WriteLine($"{group.Key}: {group.Count()} items");
}

// 分组带投影
var categoryTotals = sales
    .GroupBy(s => s.Category)
    .Select(g => new
    {
        Category = g.Key,
        TotalSales = g.Sum(s => s.Amount),
        AverageSale = g.Average(s => s.Amount),
        Count = g.Count()
    });

// 多键分组
var grouped = sales.GroupBy(s => new { s.Category, s.Region });

// GroupBy带自定义比较器
var byNameIgnoreCase = students.GroupBy(
    s => s.Name,
    StringComparer.OrdinalIgnoreCase
);

Join - 组合集合

var customers = GetCustomers();
var orders = GetOrders();

// 内连接
var customerOrders = from c in customers
                     join o in orders on c.Id equals o.CustomerId
                     select new { c.Name, o.OrderDate, o.Total };

// 方法语法
var customerOrders2 = customers.Join(
    orders,
    c => c.Id,
    o => o.CustomerId,
    (c, o) => new { c.Name, o.OrderDate, o.Total }
);

// 左外连接
var leftJoin = from c in customers
               join o in orders on c.Id equals o.CustomerId into customerOrders
               from co in customerOrders.DefaultIfEmpty()
               select new
               {
                   Customer = c.Name,
                   OrderTotal = co?.Total ?? 0
               };

// 多连接
var fullData = from c in customers
               join o in orders on c.Id equals o.CustomerId
               join od in orderDetails on o.Id equals od.OrderId
               select new { c.Name, o.OrderDate, od.Product };

聚合操作

基本聚合

var numbers = new[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

// 计数
int count = numbers.Count();
int evenCount = numbers.Count(n => n % 2 == 0);

// 求和
int sum = numbers.Sum();
decimal totalPrice = products.Sum(p => p.Price);

// 平均
double avg = numbers.Average();
double avgGrade = students.Average(s => s.Grade);

// 最小/最大
int min = numbers.Min();
int max = numbers.Max();
var cheapest = products.MinBy(p => p.Price); // C# 9+
var mostExpensive = products.MaxBy(p => p.Price); // C# 9+

// 任意/所有
bool hasEvens = numbers.Any(n => n % 2 == 0);
bool allPositive = numbers.All(n => n > 0);

// 第一个/最后一个/单个
var first = numbers.First();
var firstEven = numbers.First(n => n % 2 == 0);
var firstOrNull = numbers.FirstOrDefault(n => n > 100); // 0
var single = numbers.Single(n => n == 5);
var last = numbers.Last();

高级聚合

public class AggregationExamples
{
    public void AdvancedAggregates()
    {
        var sales = GetSales();

        // Aggregate - 自定义累加器
        var total = sales.Aggregate(0m, (acc, sale) => acc + sale.Amount);

        // 复杂聚合
        var stats = sales.Aggregate(
            new { Sum = 0m, Count = 0 },
            (acc, sale) => new
            {
                Sum = acc.Sum + sale.Amount,
                Count = acc.Count + 1
            },
            acc => new
            {
                acc.Sum,
                acc.Count,
                Average = acc.Sum / acc.Count
            }
        );

        // 分组聚合
        var categorySummary = sales
            .GroupBy(s => s.Category)
            .Select(g => new
            {
                Category = g.Key,
                Count = g.Count(),
                Total = g.Sum(s => s.Amount),
                Average = g.Average(s => s.Amount),
                Min = g.Min(s => s.Amount),
                Max = g.Max(s => s.Amount)
            });
    }
}

集合操作

Distinct、Union、Intersect、Except

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

// Distinct - 移除重复
var unique = new[] { 1, 2, 2, 3, 3, 3 }.Distinct(); // 1, 2, 3

// DistinctBy - C# 9+
var customers = GetCustomers();
var uniqueByEmail = customers.DistinctBy(c => c.Email);

// Union - 组合并移除重复
var union = list1.Union(list2); // 1, 2, 3, 4, 5, 6, 7, 8

// Concat - 组合但不移除重复
var concatenated = list1.Concat(list2); // 1, 2, 3, 4, 5, 4, 5, 6, 7, 8

// Intersect - 共同元素
var intersection = list1.Intersect(list2); // 4, 5

// Except - 第一个中有但第二个中没有的元素
var difference = list1.Except(list2); // 1, 2, 3

// 集合操作带自定义比较器
var products1 = GetProducts();
var products2 = GetMoreProducts();
var uniqueProducts = products1.Union(products2, new ProductComparer());

自定义相等比较器

public class ProductComparer : IEqualityComparer<Product>
{
    public bool Equals(Product? x, Product? y)
    {
        if (x == null || y == null) return false;
        return x.Name.Equals(y.Name, StringComparison.OrdinalIgnoreCase);
    }

    public int GetHashCode(Product obj)
    {
        return obj.Name.ToUpperInvariant().GetHashCode();
    }
}

// 用法
var distinct = products.Distinct(new ProductComparer());

排序与分页

排序

var products = GetProducts();

// OrderBy - 升序
var ascending = products.OrderBy(p => p.Price);

// OrderByDescending
var descending = products.OrderByDescending(p => p.Price);

// ThenBy - 次级排序
var sorted = products
    .OrderBy(p => p.Category)
    .ThenByDescending(p => p.Price)
    .ThenBy(p => p.Name);

// Reverse
var reversed = products.OrderBy(p => p.Price).Reverse();

// 自定义比较器
var customSort = products.OrderBy(p => p.Name, StringComparer.OrdinalIgnoreCase);

分页

public class PaginationExamples
{
    public PagedResult<Product> GetPage(int pageNumber, int pageSize)
    {
        var query = _context.Products
            .Where(p => p.IsActive)
            .OrderBy(p => p.Name);

        var total = query.Count();

        var items = query
            .Skip((pageNumber - 1) * pageSize)
            .Take(pageSize)
            .ToList();

        return new PagedResult<Product>
        {
            Items = items,
            PageNumber = pageNumber,
            PageSize = pageSize,
            TotalCount = total,
            TotalPages = (int)Math.Ceiling(total / (double)pageSize)
        };
    }

    // 高效分页带键集
    public List<Product> GetNextPage(int? lastId, int pageSize)
    {
        var query = _context.Products.Where(p => p.IsActive);

        if (lastId.HasValue)
        {
            query = query.Where(p => p.Id > lastId.Value);
        }

        return query
            .OrderBy(p => p.Id)
            .Take(pageSize)
            .ToList();
    }
}

自定义LINQ操作符

扩展方法

public static class LinqExtensions
{
    // WhereIf - 条件过滤
    public static IEnumerable<T> WhereIf<T>(
        this IEnumerable<T> source,
        bool condition,
        Func<T, bool> predicate)
    {
        return condition ? source.Where(predicate) : source;
    }

    // Batch - 分割成块
    public static IEnumerable<List<T>> Batch<T>(
        this IEnumerable<T> source,
        int batchSize)
    {
        var batch = new List<T>(batchSize);

        foreach (var item in source)
        {
            batch.Add(item);

            if (batch.Count == batchSize)
            {
                yield return batch;
                batch = new List<T>(batchSize);
            }
        }

        if (batch.Count > 0)
        {
            yield return batch;
        }
    }

    // ForEach
    public static void ForEach<T>(this IEnumerable<T> source, Action<T> action)
    {
        foreach (var item in source)
        {
            action(item);
        }
    }

    // DistinctBy（C# 9+内置）
    public static IEnumerable<T> DistinctBy<T, TKey>(
        this IEnumerable<T> source,
        Func<T, TKey> keySelector)
    {
        var seenKeys = new HashSet<TKey>();

        foreach (var item in source)
        {
            if (seenKeys.Add(keySelector(item)))
            {
                yield return item;
            }
        }
    }
}

// 用法
var filtered = products
    .WhereIf(filterByPrice, p => p.Price > 100)
    .WhereIf(filterByCategory, p => p.Category == "Electronics");

var batches = items.Batch(100);
foreach (var batch in batches)
{
    ProcessBatch(batch);
}

性能考虑

物化

public class PerformanceExamples
{
    // ❌ 错误 - 多次枚举
    public void MultipleEnumerations(IEnumerable<Product> products)
    {
        var query = products.Where(p => p.Price > 100);

        Console.WriteLine(query.Count()); // 枚举1
        Console.WriteLine(query.Sum(p => p.Price)); // 枚举2

        foreach (var p in query) // 枚举3
        {
            Console.WriteLine(p.Name);
        }
    }

    // ✅ 正确 - 单次枚举
    public void SingleEnumeration(IEnumerable<Product> products)
    {
        var list = products.Where(p => p.Price > 100).ToList();

        Console.WriteLine(list.Count);
        Console.WriteLine(list.Sum(p => p.Price));

        foreach (var p in list)
        {
            Console.WriteLine(p.Name);
        }
    }
}

数据库查询优化

public class QueryOptimization
{
    private readonly DbContext _context;

    // ❌ 错误 - N+1查询问题
    public async Task<List<OrderDto>> GetOrdersBadAsync()
    {
        var orders = await _context.Orders.ToListAsync();

        return orders.Select(o => new OrderDto
        {
            Id = o.Id,
            // 触发每个订单的单独查询！
            CustomerName = o.Customer.Name,
            ItemCount = o.OrderLines.Count
        }).ToList();
    }

    // ✅ 正确 - 预加载
    public async Task<List<OrderDto>> GetOrdersGoodAsync()
    {
        return await _context.Orders
            .Include(o => o.Customer)
            .Include(o => o.OrderLines)
            .Select(o => new OrderDto
            {
                Id = o.Id,
                CustomerName = o.Customer.Name,
                ItemCount = o.OrderLines.Count
            })
            .ToListAsync();
    }

    // ✅ 更好 - SQL中投影
    public async Task<List<OrderDto>> GetOrdersBestAsync()
    {
        return await _context.Orders
            .Select(o => new OrderDto
            {
                Id = o.Id,
                CustomerName = o.Customer.Name,
                ItemCount = o.OrderLines.Count
            })
            .ToListAsync();
    }
}

表达式树

理解LINQ表达式树编译。

public class ExpressionTreeExamples
{
    public void ExpressionTreeDemo()
    {
        // Lambda作为委托
        Func<int, bool> isEvenDelegate = n => n % 2 == 0;

        // Lambda作为表达式树
        Expression<Func<int, bool>> isEvenExpression = n => n % 2 == 0;

        // 检查表达式结构
        var binary = (BinaryExpression)isEvenExpression.Body;
        Console.WriteLine(binary.NodeType); // Modulo

        // 编译并执行
        var compiled = isEvenExpression.Compile();
        bool result = compiled(4); // true
    }

    // 动态构建表达式
    public IQueryable<T> ApplyFilter<T>(
        IQueryable<T> query,
        string propertyName,
        object value)
    {
        var parameter = Expression.Parameter(typeof(T), "x");
        var property = Expression.Property(parameter, propertyName);
        var constant = Expression.Constant(value);
        var equality = Expression.Equal(property, constant);
        var lambda = Expression.Lambda<Func<T, bool>>(equality, parameter);

        return query.Where(lambda);
    }
}

最佳实践

1. 对复杂查询使用方法语法：比查询语法更灵活
2. 需要时使用ToList/ToArray：对将多次枚举的查询进行物化
3. 避免多次枚举：重用查询时缓存结果
4. 早期投影：选择仅需属性，尤其在使用EF时
5. 使用AsNoTracking：用于只读EF查询
6. 批处理数据库查询：对相关数据使用Include
7. 避免在循环中使用LINQ：尽可能将查询移出循环
8. 使用IQueryable进行组合：逐步构建查询
9. 考虑编译查询：对于频繁使用的EF查询
10. 分析查询：使用日志查看生成SQL

常见陷阱

1. 多次枚举：不物化查询导致重新执行
2. N+1查询：忘记在EF中包含相关实体
3. 过早物化：过早调用ToList()限制查询组合
4. 混合IEnumerable和IQueryable：强制内存中评估
5. 客户端评估：使用无法翻译为SQL的方法
6. 忽略Where子句：忘记查询是增量构建的
7. 低效排序：在过滤前排序
8. 大结果集：不使用分页或Take()
9. 闭包捕获：lambda中捕获的变量在查询运行时评估
10. 异常吞没：FirstOrDefault返回null，而非异常

何时使用

在以下情况下使用此技能：

• 在C#中查询集合
• 使用Entity Framework或LINQ to SQL
• 使用投影转换数据
• 过滤和排序数据
• 执行聚合和分组
• 连接多个数据源
• 实现分页
• 构建动态查询
• 优化查询性能
• 使用表达式树

资源

• LINQ文档
• 101 LINQ示例
• LINQ性能提示
• 表达式树
• Entity Framework LINQ