Sum on nested collections cannot be translated.

[TonyTroeff]

I have the following database structure:

1. User -> many Accounts
2. Account -> many Transfers

In my code I want to know for each user how many funds he has in each account. This can be implemented using the following selection: (user => user.Accounts.Select(account => account.Transfers.Sum(transfer => transfer.Amount)))

Steps to reproduce

This is my actual query:

var x = await this._dbContext.Users
                .Select(u => u.Accounts.Select(a => a.Transfers.Sum(t => t.Amount)).ToList())
                .ToListAsync(cancellationToken)
                .ConfigureAwait(false);

But that code leads to the following exception being thrown:

Message:
System.InvalidOperationException : The LINQ expression 'DbSet
.Where(t => EF.Property<Nullable>((EntityShaperExpression:
EntityType: Account
ValueBufferExpression:
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False
), "Id") != null && EF.Property<Nullable>((EntityShaperExpression:
EntityType: Account
ValueBufferExpression:
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False
), "Id") == EF.Property<Nullable>(t, "AccountId"))
.Sum(t => t.Amount)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Stack Trace:
QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
MethodCallExpression.Accept(ExpressionVisitor visitor)
ExpressionVisitor.Visit(Expression node)
QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
MethodCallExpression.Accept(ExpressionVisitor visitor)
ExpressionVisitor.Visit(Expression node)
RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
MethodCallExpression.Accept(ExpressionVisitor visitor)
ExpressionVisitor.Visit(Expression node)
QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
MethodCallExpression.Accept(ExpressionVisitor visitor)
ExpressionVisitor.Visit(Expression node)
RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
MethodCallExpression.Accept(ExpressionVisitor visitor)
ExpressionVisitor.Visit(Expression node)
RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
MethodCallExpression.Accept(ExpressionVisitor visitor)
ExpressionVisitor.Visit(Expression node)
QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
Database.CompileQuery[TResult](Expression query, Boolean async)
QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
<>c__DisplayClass12_01.<ExecuteAsync>b__0() CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken)
ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator() EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)

This code should be translated and successfully executed in my opinion as it works with simple collections.

Further technical details

EF Core version: 3.1.3
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE: Visual Studio 2019 Enterprise 16.5.1

[TonyTroeff]

If you want to see the code, this is the repository of my project - https://dev.azure.com/TryAtSoftware/ExpenseTracker

You can debug that test:

[ajcvickers]

@TonyTroeff The issue here is that Amount is a decimal property. See #19635.