PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – array_agg in EF Core 6

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    At the end I have created a view on top of the table with a migration. Then I used the view as a regular table in my code. That way I didn't need to "play" with EF too much.. The only downside is that for every change I need to change the view manually in SQL in a migration.

    Link for how to use views in EF core: https://medium.com/@niteshsinghal85/creating-views-with-entity-framework-core-9383a9a80543


  2. 0

    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)),
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search