EF Core从PostgreSQL实现非物化复杂聚合查询求助

我用EF Core连接PostgreSQL数据库，RequestDbModel表中存在同Id但Timestamp不同的多行数据。需要查询生成Request集合，每个Request的Items是同Id的所有数据行（按Timestamp排序），同时填充以下字段：

• Request.Status：Items中最新数据项的Status
• Request.CreatedTime：Items中最旧数据项的Timestamp
• Request.AppliedTime：Items中状态为“Approved”的最旧数据项的Timestamp
• Request.CreatorId：Items中最旧数据项的UserId
• Request.ApproverId：Items中状态为“Approved”的最旧数据项的UserId（可为null）
• Request.Comment：Items中最旧数据项的Comment

我最初用物化查询实现了需求，现在改写成了非物化的版本，但不确定这段代码是否会在服务器端执行，也想知道这个新方案是否比原方案更优。

相关实体类代码 #

// builder.HasKey(p => new { p.Id, p.Timestamp })
//         .HasName("requests_pkey");
public class RequestDbModel
{
    public long Id { get; init; }
    public required string Status { get; set; }
    public required DateTime Timestamp { get; init; }
    public required Guid UserId { get; init; } 
    public string? Comment { get; init; }
}

public sealed class Request
{
    public required long Id { get; init; }
    public required string Status { get; init; }
    public DateTime CreatedTime { get; init; }
    public DateTime? AppliedTime { get; init; }
    public required Guid CreatorId { get; init; }
    public Guid? ApproverId { get; init; }
    public string? Comment { get; init; }
    public required RequestItemHistory[] Items { get; init; }
}

public sealed class RequestItemHistory
{
    public required string Status { get; init; }
    public required DateTime Timestamp { get; init; }
    public required Guid UserId  { get; init; }
    public string? Comment { get; init; }
}

原物化实现代码 #

public IQueryable<Request> GetRequests()
{
    var groups = context.Requests.AsNoTracking()
        .GroupBy(r => r.Id)
        .Select(g => new
        {
            Id = g.Key,
            Items = g.OrderBy(r => r.Timestamp).Select(r => new RequestItemHistory
            {
                Status = r.Status,
                Timestamp = r.Timestamp,
                UserId = r.UserId,
                Comment = r.Comment,
            })//.ToList()
        });

    var result = groups
        .ToList()
        .Select(x => new Request
        {
            Id = x.Id,
            Status = x.Items.Last().Status,
            CreatedTime = x.Items.First().Timestamp,
            AppliedTime = x.Items.Where(i => i.Status == "Approved").OrderByDescending(i => i.Timestamp).Select(i => (DateTime?)i.Timestamp).FirstOrDefault(),
            CreatorId = x.Items.First().UserId,
            ApproverId = x.Items.Where(i => i.Status == "Approved").OrderByDescending(i => i.Timestamp).Select(i => i.UserId).FirstOrDefault(),
            Comment = x.Items.Last().Comment,
            Items = x.Items.ToArray()
        }).AsQueryable();

    return result;        
}

新的非物化实现代码 #

public sealed class Request
{
    public required long Id { get; init; }
    public required string Status { get; init; }
    public DateTime CreatedTime { get; init; }
    public DateTime? AppliedTime { get; init; }
    public required Guid CreatorId { get; init; }
    public Guid? ApproverId { get; init; }
    public string? Comment { get; init; }
    public required IEnumerable<RequestItemHistory> Items { get; init; }
}

public IQueryable<Request> GetRequests()
{
    var groups = context.Requests.AsNoTracking()
        .GroupBy(r => r.Id)
        .Select(g => new
        {
            Id = g.Key,
            Status = g.OrderByDescending(x => x.Timestamp).Select(x => x.Status).First(),
            CreatedTime = g.OrderBy(x => x.Timestamp).Select(x => x.Timestamp).First(),
            AppliedTime = g.Where(x => x.Status == RequestStatusConstants.Approved).OrderByDescending(x => x.Timestamp).Select(x => (DateTime?)x.Timestamp).FirstOrDefault(),
            CreatorId = g.OrderBy(x => x.Timestamp).Select(x => x.UserId).First(),
            ApproverId = g.Where(x => x.Status == RequestStatusConstants.Approved).OrderByDescending(x => x.Timestamp).Select(x => (Guid?)x.UserId).FirstOrDefault(),
            Comment = g.OrderByDescending(x => x.Timestamp).Select(x => x.Comment).First(),
            Items = g.OrderBy(r => r.Timestamp).Select(r => new RequestItemHistory
            {
                Status = r.Status,
                Timestamp = r.Timestamp,
                UserId = r.UserId,
                Comment = r.Comment,
            })
        });

    var result = groups
        .Select(x => new Request
        {
            Id = x.Id,
            Status = x.Status,
            CreatedTime = x.CreatedTime,
            AppliedTime = x.AppliedTime,
            CreatorId = x.CreatorId,
            ApproverId = x.ApproverId,
            Comment = x.Comment,
            Items = x.Items
        });

    return result;        
}

解答 #

1. 新代码是否在服务器端执行？ #

是的，新代码的所有操作都基于IQueryable，没有调用ToList()这类触发物化的方法，EF Core会将整个查询表达式转换为对应的SQL语句在PostgreSQL服务器端执行。你可以通过EF Core的日志功能查看生成的SQL，确认所有分组、排序、聚合逻辑都被转换为服务器端的查询操作。

需要注意的是：Request类的Items属性改为IEnumerable<RequestItemHistory>是关键，因为EF Core无法直接将分组后的集合映射为数组（T[]），而IEnumerable<T>可以被解析为SQL中的关联查询或数组构造（PostgreSQL支持ARRAY构造）。

2. 新方案是否比原方案更优？ #

新方案在绝大多数场景下比原方案更优，主要体现在以下几点：

• 减少数据传输：原方案调用ToList()会将所有分组后的Items数据先加载到内存，再在客户端进行字段计算；新方案直接在服务器端完成所有聚合计算，仅传输最终需要的Request数据和对应的Items集合，大幅降低网络开销。
• 利用服务器性能：数据库服务器通常针对查询优化做了大量优化（比如索引），将聚合逻辑放在服务器端执行比客户端内存计算效率更高，尤其是在数据量较大时。
• 延迟执行特性保留：新方案返回的IQueryable<Request>依然支持延迟执行，你可以在后续链式调用中添加过滤、分页等条件，这些条件会被合并到最终的SQL中，而原方案调用ToList()后就无法再利用服务器端的查询优化了。

唯一需要注意的是：如果RequestDbModel表的数据量极大，且每个Id对应的行数非常多，生成的SQL可能会包含复杂的分组和子查询，此时需要确保Id和Timestamp字段有合适的索引（比如联合索引(Id, Timestamp)），否则服务器端的查询性能可能会受影响。

内容的提问来源于stack exchange，提问作者wdtv