EF Core 6+PostgreSQL实现array_agg查询的优化方案问询

核心问题 #

你的原始LINQ代码会生成多次Join，是因为EF Core 6默认对GroupBy+ToList()的翻译会倾向于客户端或多步数据库操作，而PostgreSQL原生的array_agg可以在单次查询完成聚合，性能更优。下面提供两种可行方案：

方案1：利用Npgsql导航属性原生翻译（推荐） #

如果你的实体类已配置导航属性（EF Core最佳实践）：

• Author实体添加public ICollection<Book> Books { get; set; } = new List<Book>();
• Book实体添加public Author? Author { get; set; }

可以直接用以下LINQ查询，Npgsql会自动将ToArray()翻译为array_agg：

context.Authors
    .Where(a => a.Books.Any()) // 对应原SQL的INNER JOIN，过滤无书籍的作者
    .Select(a => new 
    {
        AuthorName = a.AuthorName,
        BookNames = a.Books.Select(b => b.BookName).ToArray()
    })

生成的SQL会和你目标的SELECT Authors.AuthorName, array_agg(Books.BookName) ...完全一致，单次查询完成聚合，性能最优。

方案2：自定义array_agg函数翻译（无导航属性场景） #

如果不想依赖导航属性，可以手动注册array_agg的EF Core翻译逻辑：

1. 定义静态扩展方法（仅用于EF Core查询翻译，运行时不会执行）：

public static class NpgsqlLinqExtensions
{
    public static T[] ArrayAgg<T>(this IEnumerable<T> source) 
        => throw new NotSupportedException("仅支持EF Core查询翻译使用");
}

2. 在DbContext的OnModelCreating中注册函数映射：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var arrayAggMethod = typeof(NpgsqlLinqExtensions).GetMethod(nameof(NpgsqlLinqExtensions.ArrayAgg))!;
    modelBuilder.HasDbFunction(arrayAggMethod)
        .HasTranslation(args => new SqlFunctionExpression(
            functionName: "array_agg",
            arguments: args,
            returnType: typeof(string[]),
            nullable: true));
}

3. 使用自定义函数编写LINQ查询：

from a in context.Authors
join b in context.Books on a.AuthorId equals b.AuthorId
group b.BookName by new { a.AuthorId, a.AuthorName } into g
select new 
{
    AuthorName = g.Key.AuthorName,
    BookNames = g.ArrayAgg()
};

此方案会让EF Core直接生成包含array_agg的SQL，避免多次Join操作，保证数据库端聚合的性能。

原代码性能差的原因 #

你原代码中的g.Select(name => name).ToList()会触发EF Core的客户端评估或生成额外的Left Join查询，将聚合逻辑放到了应用端而非数据库端，导致性能损耗。上面的两种方案均是让聚合逻辑在数据库端通过array_agg完成，大幅提升查询效率。

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