array_agg in EF Core 6

Question

I'm working with EF Core 6 and PostgreSQL, and I need to construct a query that efficiently retrieves author names along with an array of their book titles.

My database schema:

Books

1. BookId (number)
2. AuthorId (number)
3. BookName (string)

Authors

1. AuthorId (number)
2. AuthorName (string)

The SQL query I'm trying to achieve is:

SELECT Authors.AuthorName, array_agg(Books.BookName)
FROM Authors
JOIN Books ON Books.AuthorId = Authors.AuthorId
GROUP BY Authors.AuthorId

Since EF Core 6 doesn't directly support array_agg, how can I translate this into C# LINQ to SQL while prioritizing performance?

Here's what I've tried so far:

C#

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

Currently everything I tried when using Group By translated to join and then another leftJoin.

Is there a way for me to add a translation to array_agg?

Any help or suggestions would be greatly appreciated!

Answer 1

It's pretty easy to just add your own custom translator for array_agg. In EF Core 7 this has now been added, but you can create a custom one yourself.

Copying bits from the official code, you first need a function you can put into the expression

public static class MyAggregateDbFunctionsExtensions
{
    public static T[] ArrayAgg<T>(this DbFunctions _, IEnumerable<T> input) =>
        throw new NotImplementedException();
}

Then a translator to create a SqlExpression from the query Expression tree.

public class MyAggregateMethodTranslator(
    NpgsqlSqlExpressionFactory sqlExpressionFactory,
    IRelationalTypeMappingSource typeMappingSource,
    IModel model
) : IAggregateMethodCallTranslator
{
    private NpgsqlSqlExpressionFactory _sqlExpressionFactory = sqlExpressionFactory;
    private IRelationalTypeMappingSource _typeMappingSource = typeMappingSource;
    private IModel _model;

    public virtual SqlExpression? Translate(
        MethodInfo method,
        EnumerableExpression source,
        IReadOnlyList<SqlExpression> arguments,
        IDiagnosticsLogger<DbLoggerCategory.Query> logger)
    {
        if (source.Selector is not SqlExpression sqlExpression)
            return null;

        if (method.DeclaringType == typeof(MyAggregateDbFunctionsExtensions)
            && method.Name == nameof(MyAggregateDbFunctionsExtensions.ArrayAgg))
        {
            return _sqlExpressionFactory.AggregateFunction(
                        "array_agg",
                        [sqlExpression],
                        source,
                        nullable: true,
                        argumentsPropagateNullability: [false],
                        returnType: method.ReturnType,
                        typeMapping: sqlExpression.TypeMapping is null
                            ? null
                            : _typeMappingSource.FindMapping(method.ReturnType, _model, sqlExpression.TypeMapping));
        }

        return null;
    }
}

Then a plugin to offer up that translator. You will add this to your ServicesCollection via Dependency Injection:

public class MyAggregateMethodCallTranslatorPlugin : IAggregateMethodCallTranslatorPlugin
{
    public MyAggregateMethodCallTranslatorPlugin(
        ISqlExpressionFactory sqlExpressionFactory,
        IRelationalTypeMappingSource typeMappingSource,
        IModel model
    )
    {
        Translators = [
            new MyAggregateMethodTranslator((NpgsqlSqlExpressionFactory) sqlExpressionFactory, typeMappingSource, model),
        ];
    }

    public IEnumerable<IAggregateMethodCallTranslator> Translators { get; }
}

You add it to the ServiceCollection

services.AddScoped<IAggregateMethodCallTranslatorPlugin, MyAggregateMethodCallTranslatorPlugin>();

Then you can use it in your query like this

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