Translate custom aggregate functions without GroupBy

[roji]

Max can be used over a collection navigation in a projection:

_ = await ctx.Authors
    .Select(a => new { Author = a, Books = a.Books.Max(b => b.Id) })
    .ToArrayAsync();

SELECT [a].[Id], [a].[Name], (
    SELECT MAX([b].[Id])
    FROM [Books] AS [b]
    WHERE [a].[Id] = [b].[AuthorId]) AS [Books]
FROM [Authors] AS [a]

However, the same can't be done with string.Join:

_ = await ctx.Authors
    .Select(a => new { Author = a, Books = string.Join(", ", a.Books.Select(b => b.Name)) })
    .ToArrayAsync();

(this causes client evaluation)

The query above can be rewritten using a GroupBy, which does work:

_ = await ctx.Books
    .GroupBy(b => b.Author)
    .Select(g => new { Author = g.Key, Books = string.Join(", ", g.Select(b => b.Name)) })
    .ToArrayAsync();

[ajcvickers]

This seems to be impacting statistical aggregate functions as well. Unless I do a GroupBy, I always get:

System.InvalidOperationException: The 'VariancePopulation' method is not supported because the query has switched to client-evaluation. This usually happens when the arguments to the method cannot be translated to server. Rewrite the query to avoid client evaluation of arguments so that method can be tran
slated to server.

[ajcvickers]

Also, just attempting to use the aggregate function is enough to force the switch to client evaluation (and fail) even if client eval would not be used without the aggregate. For example:

var query = context.Authors
    .Select(author => new
    {
        Author = author,
        TotalCost = author.Books.Sum(book => book.Detail.Price),
        // VariancePopulation = EF.Functions.VariancePopulation(author.Books.Select(post => post.Detail.Price)),
    });

Generates:

      SELECT [a].[Id], [a].[Name], (
          SELECT COALESCE(SUM([b].[Detail_Price]), 0)
          FROM [Books] AS [b]
          WHERE [a].[Id] = [b].[AuthorId]) AS [TotalCost]
      FROM [Authors] AS [a]

But uncommenting the function call causes the client-eval warning.

[roji]

Also, just attempting to use the aggregate function is enough to force the switch to client evaluation (and fail) even if client eval would not be used without the aggregate. For example:

Right, I think that's expected given there's a translation failure with the custom aggregates (regular switch to client eval on failure in top projection).

[ajcvickers]

Note from triage: this is a limitation based on the aggregate function having only an IEnumerable overload, as opposed to methods like Max which also have an IQueryable overload and are there for handled differently in the expression tree. We will document this limitation for 7.0.

[roji]

Another instance: npgsql/efcore.pg#2569

[aradalvand]

Would be nice if this got planned for EF8, seems like one of those things that's pretty basic (in that you would expect it to work as a user), but currently lacking.