Translate array_agg over records/tuples

[roji]

We support array_agg over a single scalar:

_ = ctx.Posts
    .GroupBy(p => p.Blog)
    .Select(g => g.Max(p => p.Id))
    .ToArray();

We should also be able to translate this over multiple scalars:

_ = ctx.Posts
    .GroupBy(p => p.Blog)
    .Select(g => EF.Functions.ArrayAgg(g.Select(p => new { p.Id, p.Title })))
    .ToArray();

Raised in #2631

[swimmesberger]

I experimented with array_agg a little bit and tried to convert following SQL Statement:

SELECT 
ARRAY_AGG(DISTINCT "Diagnostic"."DeviceEvents"."Origin") AS origins,
ARRAY_AGG(DISTINCT "Diagnostic"."DeviceEvents"."Device_DeviceId") AS deviceIds
FROM "Diagnostic"."DeviceEvents"

into LINQ.

I ended up with following statement:

        var result = await _diagnosticsDbContext.DeviceEvents
            .GroupBy(x => 1)
            .Select(x => new {
                Origins = EF.Functions.ArrayAgg(x.Select(y => y.Origin).Distinct().OrderBy(y => y))
            }).FirstOrDefaultAsync(cancellationToken);

The GroupBy with the Dummy property is used to have multiple aggregate statements similar to that issue: https://github.com/dotnet/efcore/issues/27117

To my surprise this worked and resulted in following SQL:

      SELECT array_agg(DISTINCT t."Origin" ORDER BY t."Origin") AS "Origins"
      FROM (
          SELECT d."Origin", 1 AS "Key"
          FROM "Diagnostic"."DeviceEvents" AS d
      ) AS t
      GROUP BY t."Key"
      LIMIT 1

Thats not exactly the SQL I started with but should work as expected.

Then I added my second aggregation which is part of an owned type:

        var result = await _diagnosticsDbContext.DeviceEvents
            .GroupBy(x => 1)
            .Select(x => new {
                Origins = EF.Functions.ArrayAgg(x.Select(y => y.Origin).Distinct().OrderBy(y => y)),
                DeviceIds = EF.Functions.ArrayAgg(x.Select(y => y.Device.DeviceId).Distinct().OrderBy(y => y))
            }).FirstOrDefaultAsync(cancellationToken);

This unfortunately resulted in following translation exception:

      System.InvalidOperationException: Translation of 'EF.Property<DeviceInformationData>(StructuralTypeShaperExpression: 
          X.Entities.DeviceMessageEvent
          ValueBufferExpression:
              ProjectionBindingExpression: EmptyProjectionMember
          IsNullable: False
      , "Device")' failed. Either the query source is not an entity type, or the specified property does not exist on the entity type.
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMember(MemberExpression memberExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.ProcessOrderByThenBy(EnumerableExpression enumerableExpression, LambdaExpression lambdaExpression, Boolean thenBy, Boole
an ascending)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TryTranslateAsEnumerableExpression(Expression expression, EnumerableExpression& enumerableExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateProjection(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
         at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellati
onToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

So I concluded that EF.Functions.ArrayAgg does not work correctly with owned types?