csharp-linq

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

C# LINQ

Master Language Integrated Query (LINQ) for querying and transforming data in C#. This skill covers query syntax, method syntax, deferred execution, performance optimization, and advanced LINQ patterns from C# 8-12.

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

LINQ Query Syntax vs Method Syntax

LINQ查询语法与方法语法

LINQ supports two syntaxes: query syntax (SQL-like) and method syntax (fluent). Both compile to the same code.

LINQ支持两种语法：类SQL的查询语法和流畅式的方法语法。两种语法最终会编译为相同的代码。

Query Syntax

查询语法

csharp

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 }
};

// Query syntax - SQL-like
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}");
}

csharp

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}");
}

Method Syntax

方法语法

csharp

// Method syntax - fluent API
var topStudents = students
    .Where(s => s.Grade >= 80)
    .OrderByDescending(s => s.Grade)
    .Select(s => new { s.Name, s.Grade });

// Method syntax is more flexible for complex queries
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);

csharp

// 方法语法 - 流畅式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);

When to Use Each Syntax

语法选择场景

csharp

// Query syntax better for joins
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 };

// Method syntax better for chaining and complex logic
var query2 = students
    .Where(s => s.Age >= 20)
    .SelectMany(s => s.Courses)
    .Where(c => c.Grade > 80)
    .Distinct()
    .Take(10);

// Mixed approach
var query3 = (from s in students
              where s.Age >= 20
              select s)
    .Take(10)
    .ToList(); // Force execution

csharp

// 查询语法更适合连接操作
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(); // 强制立即执行

Deferred Execution

延迟执行

LINQ queries use deferred execution - they don't execute until enumerated.

LINQ查询采用延迟执行机制——直到被枚举时才会执行。

Understanding Deferred Execution

理解延迟执行

csharp

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

// Query is defined but NOT executed
var query = numbers.Where(n => n > 2);

// Add more numbers
numbers.Add(6);
numbers.Add(7);

// Query executes NOW when enumerated
foreach (var num in query) // Gets: 3, 4, 5, 6, 7
{
    Console.WriteLine(num);
}

// Query executes AGAIN (sees current state)
var count = query.Count(); // 5

// Force immediate execution with ToList(), ToArray(), etc.
var snapshot = numbers.Where(n => n > 2).ToList();
numbers.Add(8);
Console.WriteLine(snapshot.Count); // Still 5, not 6

csharp

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

Deferred vs Immediate Execution

延迟执行与立即执行对比

csharp

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

        // Deferred - query not executed yet
        var deferred = data.Where(x => x > 2);

        // Immediate - query executed now
        var immediate = data.Where(x => x > 2).ToList();

        // Modify source
        data.Add(6);

        Console.WriteLine(deferred.Count());  // 4 (includes 6)
        Console.WriteLine(immediate.Count()); // 3 (snapshot before 6 was added)
    }

    // Dangerous: query is rebuilt each iteration
    public void DangerousPattern()
    {
        var data = GetData(); // Expensive

        // ❌ BAD - GetData() called multiple times
        foreach (var item in GetData().Where(x => x.IsActive))
        {
            Process(item);
        }

        // ✅ GOOD - GetData() called once
        var items = GetData().Where(x => x.IsActive).ToList();
        foreach (var item in items)
        {
            Process(item);
        }
    }
}

csharp

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 vs IQueryable

IEnumerable与IQueryable

IEnumerable executes in memory (LINQ to Objects). IQueryable translates to expression trees for remote execution (LINQ to SQL, EF).

IEnumerable在内存中执行（LINQ to Objects）。IQueryable会将查询转换为表达式树，用于远程执行（如LINQ to SQL、EF）。

IEnumerable<T> - In-Memory

IEnumerable<T> - 内存中执行

csharp

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 - executes in memory
        IEnumerable<Product> query = products
            .Where(p => p.Price > 50)
            .OrderBy(p => p.Name);

        // All filtering happens in C# code
        foreach (var product in query)
        {
            Console.WriteLine($"{product.Name}: ${product.Price}");
        }
    }
}

csharp

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> - Expression Trees

IQueryable<T> - 表达式树

csharp

public class QueryableExamples
{
    private readonly DbContext _context;

    // IQueryable - translates to SQL
    public async Task<List<Product>> GetExpensiveProductsAsync()
    {
        // Query builds expression tree
        IQueryable<Product> query = _context.Products
            .Where(p => p.Price > 50)
            .OrderBy(p => p.Name);

        // SQL generated and executed here
        return await query.ToListAsync();
        // SQL: SELECT * FROM Products WHERE Price > 50 ORDER BY Name
    }

    // Composable queries
    public IQueryable<Product> GetActiveProducts()
    {
        return _context.Products.Where(p => p.IsActive);
    }

    public async Task<List<Product>> GetExpensiveActiveProductsAsync()
    {
        // Compose queries - still generates single SQL
        var products = await GetActiveProducts()
            .Where(p => p.Price > 100)
            .ToListAsync();

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

csharp

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;
    }
}

Mixing IEnumerable and IQueryable

混合使用IEnumerable与IQueryable

csharp

public class MixingQueries
{
    private readonly DbContext _context;

    public async Task<List<ProductDto>> GetProductsDangerousAsync()
    {
        // ❌ BAD - ToList() brings ALL products to memory first
        var products = await _context.Products.ToListAsync();

        // Then filters in memory (inefficient)
        return products
            .Where(p => p.Price > 100) // In memory
            .Select(p => new ProductDto { Name = p.Name })
            .ToList();
    }

    public async Task<List<ProductDto>> GetProductsEfficientAsync()
    {
        // ✅ GOOD - everything in SQL
        return await _context.Products
            .Where(p => p.Price > 100) // In SQL
            .Select(p => new ProductDto { Name = p.Name }) // In SQL
            .ToListAsync(); // Execute once
    }

    public async Task<List<Product>> ComplexFilterAsync()
    {
        // ✅ GOOD - SQL where possible, memory when necessary
        return await _context.Products
            .Where(p => p.Price > 50) // SQL
            .ToListAsync() // Execute SQL
            .ContinueWith(t => t.Result
                .Where(p => ComplexInMemoryCheck(p)) // C# predicate
                .ToList()
            );
    }

    private bool ComplexInMemoryCheck(Product product)
    {
        // Logic that can't be translated to SQL
        return product.Name.Split(' ').Length > 2;
    }
}

csharp

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;
    }
}

Common LINQ Operators

常用LINQ操作符

Where - Filtering

Where - 筛选

csharp

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

// Simple filter
var evens = numbers.Where(n => n % 2 == 0);

// Multiple conditions
var filtered = numbers.Where(n => n > 10 && n < 50 && n % 3 == 0);

// Filter with index
var everyThird = numbers.Where((n, index) => index % 3 == 0);

// Complex filtering
var products = GetProducts()
    .Where(p => p.IsActive)
    .Where(p => p.Price >= 10 && p.Price <= 100)
    .Where(p => p.Category.StartsWith("Electronics"));

csharp

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 - Projection

Select - 投影

csharp

var students = GetStudents();

// Simple projection
var names = students.Select(s => s.Name);

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

// Projection with index
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
});

csharp

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
});

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

SelectMany - Flattening

SelectMany - 扁平化

csharp

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" }
        }
    }
};

// Flatten nested collections
var allEmployees = departments.SelectMany(d => d.Employees);

// With result selector
var employeesWithDept = departments.SelectMany(
    dept => dept.Employees,
    (dept, emp) => new { Department = dept.Name, Employee = emp.Name }
);

// Multiple levels
var orders = GetOrders();
var allItems = orders
    .SelectMany(o => o.OrderLines)
    .SelectMany(ol => ol.Items);

csharp

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 - Grouping

GroupBy - 分组

csharp

var sales = GetSales();

// Simple grouping
var byCategory = sales.GroupBy(s => s.Category);

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

// Grouping with projection
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()
    });

// Multiple key grouping
var grouped = sales.GroupBy(s => new { s.Category, s.Region });

// GroupBy with custom comparer
var byNameIgnoreCase = students.GroupBy(
    s => s.Name,
    StringComparer.OrdinalIgnoreCase
);

csharp

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 });

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

Join - Combining Collections

Join - 集合关联

csharp

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

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

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

// Left outer join
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
               };

// Multiple joins
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 };

csharp

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 };

Aggregation Operations

聚合操作

Basic Aggregations

基础聚合

csharp

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

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

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

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

// Min/Max
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+

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

// First/Last/Single
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();

csharp

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();

Advanced Aggregations

高级聚合

csharp

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

        // Aggregate - custom accumulator
        var total = sales.Aggregate(0m, (acc, sale) => acc + sale.Amount);

        // Complex aggregation
        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
            }
        );

        // Grouped aggregations
        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)
            });
    }
}

csharp

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)
            });
    }
}

Set Operations

集合操作

Distinct, Union, Intersect, Except

csharp

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

// Distinct - remove duplicates
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 - combine and remove duplicates
var union = list1.Union(list2); // 1, 2, 3, 4, 5, 6, 7, 8

// Concat - combine without removing duplicates
var concatenated = list1.Concat(list2); // 1, 2, 3, 4, 5, 4, 5, 6, 7, 8

// Intersect - common elements
var intersection = list1.Intersect(list2); // 4, 5

// Except - elements in first but not second
var difference = list1.Except(list2); // 1, 2, 3

// Set operations with custom comparer
var products1 = GetProducts();
var products2 = GetMoreProducts();
var uniqueProducts = products1.Union(products2, new ProductComparer());

csharp

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());

Custom Equality Comparers

自定义相等比较器

csharp

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();
    }
}

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

csharp

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());

Ordering and Pagination

排序与分页

Ordering

排序

csharp

var products = GetProducts();

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

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

// ThenBy - secondary sort
var sorted = products
    .OrderBy(p => p.Category)
    .ThenByDescending(p => p.Price)
    .ThenBy(p => p.Name);

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

// Custom comparer
var customSort = products.OrderBy(p => p.Name, StringComparer.OrdinalIgnoreCase);

csharp

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);

Pagination

分页

csharp

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)
        };
    }

    // Efficient pagination with keyset
    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();
    }
}

csharp

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();
    }
}

Custom LINQ Operators

自定义LINQ操作符

Extension Methods

扩展方法

csharp

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

    // Batch - split into chunks
    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 (built-in in 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;
            }
        }
    }
}

// Usage
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);
}

csharp

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);
}

Performance Considerations

性能注意事项

Materialization

数据物化

csharp

public class PerformanceExamples
{
    // ❌ BAD - Multiple enumerations
    public void MultipleEnumerations(IEnumerable<Product> products)
    {
        var query = products.Where(p => p.Price > 100);

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

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

    // ✅ GOOD - Single enumeration
    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);
        }
    }
}

csharp

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

        Console.WriteLine(query.Count()); // 第一次枚举
        Console.WriteLine(query.Sum(p => p.Price)); // 第二次枚举

        foreach (var p in query) // 第三次枚举
        {
            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);
        }
    }
}

Database Query Optimization

数据库查询优化

csharp

public class QueryOptimization
{
    private readonly DbContext _context;

    // ❌ BAD - N+1 query problem
    public async Task<List<OrderDto>> GetOrdersBadAsync()
    {
        var orders = await _context.Orders.ToListAsync();

        return orders.Select(o => new OrderDto
        {
            Id = o.Id,
            // Triggers separate query for each order!
            CustomerName = o.Customer.Name,
            ItemCount = o.OrderLines.Count
        }).ToList();
    }

    // ✅ GOOD - Eager loading
    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();
    }

    // ✅ BETTER - Project in 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();
    }
}

csharp

public class QueryOptimization
{
    // ❌ 错误 - 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();
    }
}

Expression Trees

表达式树

Understanding LINQ's expression tree compilation.

csharp

public class ExpressionTreeExamples
{
    public void ExpressionTreeDemo()
    {
        // Lambda as delegate
        Func<int, bool> isEvenDelegate = n => n % 2 == 0;

        // Lambda as expression tree
        Expression<Func<int, bool>> isEvenExpression = n => n % 2 == 0;

        // Examine expression structure
        var binary = (BinaryExpression)isEvenExpression.Body;
        Console.WriteLine(binary.NodeType); // Modulo

        // Compile and execute
        var compiled = isEvenExpression.Compile();
        bool result = compiled(4); // true
    }

    // Building expressions dynamically
    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);
    }
}

理解LINQ的表达式树编译机制。

csharp

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);
    }
}

Best Practices

最佳实践

Use Method Syntax for Complex Queries: More flexible than query syntax
ToList/ToArray When Needed: Materialize queries you'll enumerate multiple times
Avoid Multiple Enumerations: Cache results when reusing queries
Project Early: Select only needed properties, especially with EF
Use AsNoTracking: For read-only EF queries
Batch Database Queries: Use Include for related data
Avoid LINQ in Loops: Pull queries out of loops when possible
Use IQueryable for Composition: Build queries gradually
Consider Compiled Queries: For frequently-used EF queries
Profile Your Queries: Use logging to see generated SQL

复杂查询使用方法语法：比查询语法更灵活
必要时使用ToList/ToArray：对需要多次枚举的查询进行物化
避免多次枚举：复用查询时缓存结果
尽早投影：尤其是使用EF时，只选择需要的属性
使用AsNoTracking：针对只读EF查询
批量处理数据库查询：使用Include加载关联数据
避免在循环中使用LINQ：尽可能将查询移出循环
使用IQueryable进行组合：逐步构建查询
考虑编译查询：针对频繁使用的EF查询
分析查询性能：通过日志查看生成的SQL

Common Pitfalls

常见陷阱

Multiple Enumerations: Not materializing queries leads to re-execution
N+1 Queries: Forgetting to Include related entities in EF
Premature Materialization: Calling ToList() too early limits query composition
Mixing IEnumerable and IQueryable: Forces in-memory evaluation
Client-Side Evaluation: Using methods that can't translate to SQL
Ignored Where Clauses: Forgetting queries build incrementally
Inefficient Ordering: Ordering before filtering
Large Result Sets: Not using pagination or Take()
Closure Capturing: Variables captured in lambdas evaluated when query runs
Exception Swallowing: FirstOrDefault returns null, not an exception

多次枚举：未物化查询会导致重复执行
N+1查询：在EF中忘记Include关联实体
过早物化：过早调用ToList()会限制查询组合能力
混合IEnumerable与IQueryable：会强制在内存中执行
客户端评估：使用无法转换为SQL的方法
忽略Where子句的增量构建：忘记查询是逐步构建的
低效排序：筛选前先排序
大结果集：未使用分页或Take()
闭包捕获：Lambda中捕获的变量在查询执行时才会被求值
异常吞没有：FirstOrDefault返回null而非抛出异常

When to Use

使用场景

Use this skill when:

Querying collections in C#
Working with Entity Framework or LINQ to SQL
Transforming data with projections
Filtering and sorting data
Performing aggregations and grouping
Joining multiple data sources
Implementing pagination
Building dynamic queries
Optimizing query performance
Working with expression trees

在以下场景使用本技能：

在C#中查询集合数据
处理Entity Framework或LINQ to SQL
使用投影转换数据
筛选和排序数据
执行聚合和分组操作
关联多个数据源
实现分页功能
构建动态查询
优化查询性能
处理表达式树

Resources

参考资源

LINQ Documentation
101 LINQ Samples
LINQ Performance Tips
Expression Trees
Entity Framework LINQ

LINQ官方文档
101个LINQ示例
LINQ性能优化技巧
表达式树
Entity Framework LINQ