复杂查询: 分组/聚合/联接

概述

在实际业务中,简单的 CRUD 操作往往无法满足需求。复杂的统计分析、数据汇总、多表关联等场景需要使用分组(GroupBy)、聚合(Aggregate)和联接(Join)等高级查询技术。EF Core 将这些 LINQ 操作转换为高效的 SQL 查询,在数据库层面完成计算,避免将大量数据加载到内存中。

分组查询 (GroupBy)

基础分组

csharp

// 按分类统计产品数量
var productCountByCategory = await context.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new 
    { 
        CategoryId = g.Key,
        Count = g.Count()
    })
    .ToListAsync();

// 生成的 SQL:
// SELECT [p].[CategoryId], COUNT(*) AS [Count]
// FROM [Products] AS [p]
// GROUP BY [p].[CategoryId]

多字段分组

csharp

// 按年份和月份统计订单数
var ordersByMonth = await context.Orders
    .GroupBy(o => new 
    { 
        o.OrderDate.Year, 
        o.OrderDate.Month 
    })
    .Select(g => new 
    { 
        Year = g.Key.Year,
        Month = g.Key.Month,
        OrderCount = g.Count(),
        TotalAmount = g.Sum(o => o.TotalAmount)
    })
    .OrderBy(x => x.Year)
    .ThenBy(x => x.Month)
    .ToListAsync();

// 生成的 SQL:
// SELECT YEAR([o].[OrderDate]) AS [Year], 
//        MONTH([o].[OrderDate]) AS [Month], 
//        COUNT(*) AS [OrderCount], 
//        SUM([o].[TotalAmount]) AS [TotalAmount]
// FROM [Orders] AS [o]
// GROUP BY YEAR([o].[OrderDate]), MONTH([o].[OrderDate])
// ORDER BY [Year], [Month]

分组后过滤 (Having)

csharp

// 找出订单数超过10的客户
var highValueCustomers = await context.Orders
    .GroupBy(o => o.CustomerId)
    .Where(g => g.Count() > 10)  // Having 子句
    .Select(g => new 
    { 
        CustomerId = g.Key,
        OrderCount = g.Count(),
        AvgOrderValue = g.Average(o => o.TotalAmount)
    })
    .OrderByDescending(x => x.OrderCount)
    .ToListAsync();

// 生成的 SQL:
// SELECT [o].[CustomerId], 
//        COUNT(*) AS [OrderCount], 
//        AVG([o].[TotalAmount]) AS [AvgOrderValue]
// FROM [Orders] AS [o]
// GROUP BY [o].[CustomerId]
// HAVING COUNT(*) > CAST(10 AS bigint)
// ORDER BY [OrderCount] DESC

分组并获取详细信息

csharp

// 每个分类中最贵的产品
var expensiveProductsPerCategory = await context.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new 
    { 
        CategoryId = g.Key,
        MaxPrice = g.Max(p => p.Price),
        MostExpensiveProduct = g.OrderByDescending(p => p.Price).FirstOrDefault()
    })
    .ToListAsync();

// 注意: FirstOrDefault 在分组内可能无法转换为 SQL
// 替代方案: 使用子查询
var result = await context.Categories
    .Select(c => new 
    { 
        c.Id,
        c.Name,
        MostExpensiveProduct = context.Products
            .Where(p => p.CategoryId == c.Id)
            .OrderByDescending(p => p.Price)
            .FirstOrDefault()
    })
    .ToListAsync();

聚合函数 (Aggregate Functions)

基本聚合

csharp

// 单个聚合值
var totalProducts = await context.Products.CountAsync();
var averagePrice = await context.Products.AverageAsync(p => p.Price);
var maxPrice = await context.Products.MaxAsync(p => p.Price);
var minPrice = await context.Products.MinAsync(p => p.Price);
var totalStock = await context.Products.SumAsync(p => p.Stock);

// 组合聚合
var stats = await context.Products
    .Select(p => new 
    { 
        Count = context.Products.Count(),
        AveragePrice = context.Products.Average(p2 => p2.Price),
        MaxPrice = context.Products.Max(p2 => p2.Price),
        MinPrice = context.Products.Min(p2 => p2.Price),
        TotalValue = context.Products.Sum(p2 => p2.Price * p2.Stock)
    })
    .FirstOrDefaultAsync();

条件聚合

csharp

// 统计不同状态的产品数量
var statusStats = await context.Products
    .GroupBy(p => 1)  // 所有记录为一组
    .Select(g => new 
    { 
        TotalCount = g.Count(),
        ActiveCount = g.Count(p => p.IsActive),
        InactiveCount = g.Count(p => !p.IsActive),
        DiscontinuedCount = g.Count(p => p.IsDiscontinued),
        ActivePercentage = (double)g.Count(p => p.IsActive) / g.Count() * 100
    })
    .FirstOrDefaultAsync();

// 生成的 SQL (SQL Server 2012+):
// SELECT COUNT(*) AS [TotalCount],
//        SUM(CASE WHEN [p].[IsActive] = CAST(1 AS bit) THEN 1 ELSE 0 END) AS [ActiveCount],
//        SUM(CASE WHEN [p].[IsActive] = CAST(0 AS bit) THEN 1 ELSE 0 END) AS [InactiveCount],
//        ...
// FROM [Products] AS [p]

字符串聚合

csharp

// EF Core 8+ 支持 STRING_AGG
var categoryProducts = await context.Categories
    .Select(c => new 
    { 
        c.Name,
        ProductNames = context.Products
            .Where(p => p.CategoryId == c.Id)
            .Select(p => p.Name)
            .ToList()  // 注意: 这会在客户端执行
    })
    .ToListAsync();

// EF Core 8+ 使用原生 SQL
var result = await context.Database
    .SqlQueryRaw<CategoryProductDto>(
        @"SELECT c.Name, 
                 STRING_AGG(p.Name, ', ') AS ProductList
          FROM Categories c
          LEFT JOIN Products p ON c.Id = p.CategoryId
          GROUP BY c.Name")
    .ToListAsync();

联接查询 (Join)

1. INNER JOIN (内连接)

csharp

// LINQ Join 语法
var orderWithCustomer = await context.Orders
    .Join(context.Customers,
        order => order.CustomerId,
        customer => customer.Id,
        (order, customer) => new 
        { 
            OrderId = order.Id,
            OrderDate = order.OrderDate,
            CustomerName = customer.Name,
            CustomerEmail = customer.Email
        })
    .ToListAsync();

// 更推荐的导航属性方式
var orderWithCustomer2 = await context.Orders
    .Include(o => o.Customer)
    .Select(o => new 
    { 
        o.Id,
        o.OrderDate,
        o.Customer.Name,
        o.Customer.Email
    })
    .ToListAsync();

// 生成的 SQL:
// SELECT [o].[Id], [o].[OrderDate], [c].[Name], [c].[Email]
// FROM [Orders] AS [o]
// INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]

2. LEFT JOIN (左外连接)

csharp

// 使用 DefaultIfEmpty 实现 LEFT JOIN
var customersWithOrders = await context.Customers
    .GroupJoin(context.Orders,
        customer => customer.Id,
        order => order.CustomerId,
        (customer, orders) => new 
        { 
            customer.Id,
            customer.Name,
            OrderCount = orders.Count(),
            TotalSpent = orders.Sum(o => (decimal?)o.TotalAmount) ?? 0
        })
    .ToListAsync();

// 或使用导航属性(推荐)
var customersWithOrders2 = await context.Customers
    .Select(c => new 
    { 
        c.Id,
        c.Name,
        OrderCount = c.Orders.Count,
        TotalSpent = c.Orders.Sum(o => (decimal?)o.TotalAmount) ?? 0
    })
    .ToListAsync();

// 生成的 SQL:
// SELECT [c].[Id], [c].[Name], 
//        (SELECT COUNT(*) FROM [Orders] WHERE [CustomerId] = [c].[Id]) AS [OrderCount],
//        (SELECT SUM([TotalAmount]) FROM [Orders] WHERE [CustomerId] = [c].[Id]) AS [TotalSpent]
// FROM [Customers] AS [c]

3. 多表联接

csharp

// 订单 + 客户 + 产品信息
var orderDetails = await context.OrderItems
    .Include(oi => oi.Order)
        .ThenInclude(o => o.Customer)
    .Include(oi => oi.Product)
    .Select(oi => new 
    { 
        OrderId = oi.Order.Id,
        OrderDate = oi.Order.OrderDate,
        CustomerName = oi.Order.Customer.Name,
        ProductName = oi.Product.Name,
        Quantity = oi.Quantity,
        UnitPrice = oi.UnitPrice,
        TotalPrice = oi.Quantity * oi.UnitPrice
    })
    .ToListAsync();

// 生成的 SQL:
// SELECT [o].[Id], [o].[OrderDate], [c].[Name], [p].[Name], 
//        [oi].[Quantity], [oi].[UnitPrice], [oi].[Quantity] * [oi].[UnitPrice] AS [TotalPrice]
// FROM [OrderItems] AS [oi]
// INNER JOIN [Orders] AS [o] ON [oi].[OrderId] = [o].[Id]
// INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
// INNER JOIN [Products] AS [p] ON [oi].[ProductId] = [p].[Id]

4. 自联接

csharp

// 员工及其经理信息
var employeesWithManagers = await context.Employees
    .Join(context.Employees,
        emp => emp.ManagerId,
        mgr => mgr.Id,
        (emp, mgr) => new 
        { 
            EmployeeName = emp.Name,
            ManagerName = mgr.Name
        })
    .ToListAsync();

// 或使用导航属性
var employeesWithManagers2 = await context.Employees
    .Where(e => e.ManagerId.HasValue)
    .Select(e => new 
    { 
        EmployeeName = e.Name,
        ManagerName = e.Manager!.Name
    })
    .ToListAsync();

复杂组合查询

场景 1: 销售报表

csharp

public class SalesReportDto
{
    public string CategoryName { get; set; } = string.Empty;
    public int TotalOrders { get; set; }
    public int TotalQuantity { get; set; }
    public decimal TotalRevenue { get; set; }
    public decimal AverageOrderValue { get; set; }
    public string TopProduct { get; set; } = string.Empty;
}

var salesReport = await context.OrderItems
    .Include(oi => oi.Product)
        .ThenInclude(p => p.Category)
    .Include(oi => oi.Order)
    .GroupBy(oi => oi.Product.CategoryId)
    .Select(g => new SalesReportDto
    { 
        CategoryName = g.First().Product.Category.Name,
        TotalOrders = g.Select(oi => oi.OrderId).Distinct().Count(),
        TotalQuantity = g.Sum(oi => oi.Quantity),
        TotalRevenue = g.Sum(oi => oi.Quantity * oi.UnitPrice),
        AverageOrderValue = g.Average(oi => oi.Quantity * oi.UnitPrice),
        TopProduct = g.GroupBy(oi => oi.Product.Name)
            .OrderByDescending(pg => pg.Sum(oi => oi.Quantity))
            .Select(pg => pg.Key)
            .FirstOrDefault()
    })
    .OrderByDescending(r => r.TotalRevenue)
    .ToListAsync();

场景 2: 客户分群分析

csharp

var customerSegments = await context.Customers
    .Select(c => new 
    { 
        c.Id,
        c.Name,
        TotalOrders = c.Orders.Count,
        TotalSpent = c.Orders.Sum(o => (decimal?)o.TotalAmount) ?? 0,
        AvgOrderValue = c.Orders.Average(o => (decimal?)o.TotalAmount) ?? 0,
        LastOrderDate = c.Orders.Max(o => (DateTime?)o.OrderDate),
        DaysSinceLastOrder = SqlFunctions.DateDiff(
            "day", 
            c.Orders.Max(o => o.OrderDate), 
            DateTime.Now)
    })
    .Select(c => new 
    { 
        c.Id,
        c.Name,
        c.TotalOrders,
        c.TotalSpent,
        c.AvgOrderValue,
        Segment = c.TotalSpent > 10000 ? "VIP" :
                  c.TotalSpent > 5000 ? "Gold" :
                  c.TotalSpent > 1000 ? "Silver" : "Bronze"
    })
    .GroupBy(c => c.Segment)
    .Select(g => new 
    { 
        Segment = g.Key,
        CustomerCount = g.Count(),
        AvgSpent = g.Average(c => c.TotalSpent),
        AvgOrders = g.Average(c => c.TotalOrders)
    })
    .OrderByDescending(s => s.AvgSpent)
    .ToListAsync();

场景 3: 库存预警报告

csharp

var inventoryAlerts = await context.Products
    .Include(p => p.Category)
    .Include(p => p.Supplier)
    .Where(p => p.Stock <= p.ReorderLevel)
    .GroupBy(p => p.CategoryId)
    .Select(g => new 
    { 
        CategoryName = g.First().Category.Name,
        LowStockProducts = g.Count(),
        CriticalProducts = g.Count(p => p.Stock == 0),
        TotalReorderCost = g.Sum(p => (p.ReorderLevel - p.Stock) * p.UnitCost),
        Products = g.OrderByDescending(p => p.Stock)
            .Select(p => new 
            { 
                p.Name,
                p.Stock,
                p.ReorderLevel,
                SupplierName = p.Supplier.Name,
                ReorderQuantity = p.ReorderLevel - p.Stock
            })
            .ToList()
    })
    .ToListAsync();

性能优化技巧

1. 避免客户端评估

csharp

// ❌ 错误: 整个表加载到内存后分组
var allProducts = await context.Products.ToListAsync();
var grouped = allProducts.GroupBy(p => p.CategoryId);

// ✅ 正确: 在数据库端分组
var grouped = await context.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new 
    { 
        CategoryId = g.Key,
        Count = g.Count()
    })
    .ToListAsync();

2. 使用索引优化分组

sql

-- 为常用分组字段创建索引
CREATE INDEX IX_Products_CategoryId ON Products(CategoryId);
CREATE INDEX IX_Orders_CustomerId_OrderDate ON Orders(CustomerId, OrderDate);

3. 分页大数据集

csharp

// 分组结果分页
var pageNumber = 1;
var pageSize = 20;

var pagedGroups = await context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new 
    { 
        CustomerId = g.Key,
        OrderCount = g.Count(),
        TotalSpent = g.Sum(o => o.TotalAmount)
    })
    .OrderByDescending(x => x.TotalSpent)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

4. 异步聚合

csharp

// 所有聚合操作都提供异步版本
var count = await context.Products.CountAsync();
var sum = await context.Products.SumAsync(p => p.Price);
var avg = await context.Products.AverageAsync(p => p.Price);
var max = await context.Products.MaxAsync(p => p.Price);
var min = await context.Products.MinAsync(p => p.Price);

常见问题

Q1: GroupBy 返回 null?

原因: 分组后没有 Select 投影

csharp

// ❌ 错误
var groups = await context.Products.GroupBy(p => p.CategoryId).ToListAsync();

// ✅ 正确
var groups = await context.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new { CategoryId = g.Key, Count = g.Count() })
    .ToListAsync();

Q2: 如何在分组中获取第一条记录?

csharp

// 方法 1: 使用子查询
var result = await context.Categories
    .Select(c => new 
    { 
        c.Id,
        FirstProduct = context.Products
            .Where(p => p.CategoryId == c.Id)
            .OrderBy(p => p.Id)
            .FirstOrDefault()
    })
    .ToListAsync();

// 方法 2: 使用窗口函数(EF Core 8+)
var result = await context.Products
    .Select(p => new 
    { 
        p.*,
        RowNum = EF.Functions.RowNumber(p.CategoryId, p.Price)
    })
    .Where(x => x.RowNum == 1)
    .ToListAsync();

Q3: 如何优化复杂联接查询的性能?

解决方案:

确保联接字段有索引
使用 Include 而非手动 Join
只选择需要的字段(投影)
考虑拆分为多个简单查询

csharp

// ❌ 复杂联接
var result = await context.A
    .Join(context.B, ...)
    .Join(context.C, ...)
    .Join(context.D, ...)
    .ToListAsync();

// ✅ 拆分查询
var aList = await context.A.ToListAsync();
var bDict = await context.B.ToDictionaryAsync(b => b.AId);
var cDict = await context.C.ToDictionaryAsync(c => c.BId);

// 在内存中组装
var result = aList.Select(a => new 
{ 
    A = a,
    B = bDict.GetValueOrDefault(a.Id),
    C = cDict.GetValueOrDefault(bDict[a.Id]?.Id)
}).ToList();

总结

核心要点

操作	LINQ 方法	SQL 对应	用途
分组	GroupBy	GROUP BY	数据分类统计
计数	Count	COUNT(*)	记录数量
求和	Sum	SUM()	数值累加
平均	Average	AVG()	计算均值
最大/最小	Max/Min	MAX()/MIN()	极值查找
内连接	Join	INNER JOIN	匹配记录
左连接	GroupJoin + DefaultIfEmpty	LEFT JOIN	保留主表

最佳实践

✅ 推荐做法:

使用导航属性代替手动 Join
在数据库端完成分组和聚合
为常用查询字段创建索引
使用异步方法提升并发性能
只选择需要的字段

❌ 避免的陷阱:

不要在客户端进行大规模分组
避免 N+1 查询问题
不要忽略空值处理
谨慎使用复杂嵌套查询

掌握复杂查询技术,能够高效处理各种业务场景! 🚀

复杂查询: 分组/聚合/联接 ​

概述 ​

分组查询 (GroupBy) ​

基础分组 ​

多字段分组 ​

分组后过滤 (Having) ​

分组并获取详细信息 ​

聚合函数 (Aggregate Functions) ​

基本聚合 ​

条件聚合 ​

字符串聚合 ​

联接查询 (Join) ​

1. INNER JOIN (内连接) ​

2. LEFT JOIN (左外连接) ​

3. 多表联接 ​

4. 自联接 ​

复杂组合查询 ​

场景 1: 销售报表 ​

场景 2: 客户分群分析 ​

场景 3: 库存预警报告 ​

性能优化技巧 ​

1. 避免客户端评估 ​

2. 使用索引优化分组 ​

3. 分页大数据集 ​

4. 异步聚合 ​

常见问题 ​

Q1: GroupBy 返回 null? ​

Q2: 如何在分组中获取第一条记录? ​

Q3: 如何优化复杂联接查询的性能? ​

总结 ​

核心要点 ​

最佳实践 ​

复杂查询: 分组/聚合/联接

概述

分组查询 (GroupBy)

基础分组

多字段分组

分组后过滤 (Having)

分组并获取详细信息

聚合函数 (Aggregate Functions)

基本聚合

条件聚合

字符串聚合

联接查询 (Join)

1. INNER JOIN (内连接)

2. LEFT JOIN (左外连接)

3. 多表联接

4. 自联接

复杂组合查询

场景 1: 销售报表

场景 2: 客户分群分析

场景 3: 库存预警报告

性能优化技巧

1. 避免客户端评估

2. 使用索引优化分组

3. 分页大数据集

4. 异步聚合

常见问题

Q1: GroupBy 返回 null?

Q2: 如何在分组中获取第一条记录?

Q3: 如何优化复杂联接查询的性能?

总结

核心要点

最佳实践