0

I am trying to do quite a simple group by, and sum, with EF Core 3.0

However am getting a strange error:

System.InvalidOperationException: 'Processing of the LINQ expression 'AsQueryable((Unhandled parameter: y).TransactionLines)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core.

 var creditBalances = await context.Transaction
                .Include(x => x.TransactionLines)
                .Include(x=>x.CreditAccount)
                .Where(x => x.CreditAccount.UserAccount.Id == userAccount.Id)
                .GroupBy(x => new
                {
                    x.CreditAccount.ExternalId
                })
                .Select(x => new
                {
                    x.Key.ExternalId,
                    amount = x.Sum(y => y.TransactionLines.Sum(z => z.Amount))
                })
                .ToListAsync();

I'm battling to see where an issue can arise, so not even sure where to start. I am trying to get a sum of all the transaction amounts (Which is a Sum of all the TransactionLines for each transaction - i.e. A Transaction amount is made of the lines associated to it).

I then sum up all the transactions, grouping by then CreditAccount ID.

The line, Unhandled parameter: y is worrying. Maybe my grouping and summing is out.

Craig
  • 18,074
  • 38
  • 147
  • 248
  • 1
    Is `amuont` correctly spelled? – Dennis VW Nov 17 '19 at 07:21
  • Nope. :| Good spot, but unrelated to the issue. Thanks. Fixed the spelling. :) – Craig Nov 17 '19 at 07:26
  • Probably unrelated to your issue as well, but I feel that `x.Key.CreditAccount` is missing a `Id =` or `key =`, or something like that. – Dennis VW Nov 17 '19 at 07:45
  • Thank you @Dennis1679 - . Just to eliminate any issues, I changed it to reflect that. – Craig Nov 17 '19 at 07:50
  • What is related to your issue though, `.sum` will be evaluated on the client, not in de database. `x.Sum(y => y.TransactionLines.Sum(z => z.Amount)` feels to me that it's causing EF Core to throw a runtime exception. To explicitly allow client side evaluation, and to confirm this, add `.AsAsyncEnumerable()` before the first include. – Dennis VW Nov 17 '19 at 07:58
  • I add `context.Transaction.AsAsyncEnumerable()`, but the Include is then invalid. – Craig Nov 17 '19 at 08:00
  • Sorry, might be that you have to put it after the includes but before where. – Dennis VW Nov 17 '19 at 08:01
  • Added after the last include, and before the Where ... but the Where is now Invalid. `.Include(x=>x.CreditAccount) .AsAsyncEnumerable() .Where(x => x.CreditAccount.UserAccount.Id == userAccount.Id)` – Craig Nov 17 '19 at 08:05
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/202506/discussion-between-dennis1679-and-craig). – Dennis VW Nov 17 '19 at 08:09
  • 1
    It's probably the nested `Sum`. Just remove one level by starting the query at `context.TransactionLines`. – Gert Arnold Nov 17 '19 at 11:35
  • It's indeed currently bug/limitation of the nested aggregate translation. – Ivan Stoev Nov 17 '19 at 12:01

1 Answers1

2

So start at the TransactionLines level and this is as simple as:

var q = from c in context.TransactionLines
        where c.Transaction.CreditAccount.UserAccount.Id == userAccount.Id
        group c by c.Transaction.CreditAccount.ExternalId into g
        select new
        {
            ExternalId = g.Key,
            Amount = g.Sum(x => x.Amount)
        };

var creditBalances = await q.ToListAsync();

( You don't need any Include() since you're not returning an Entity with related data. You're projecting a custom data shape. )

Which translates to:

SELECT [c].[ExternalId], SUM([t].[Amount]) AS [Amount]
FROM [TransactionLines] AS [t]
LEFT JOIN [Transaction] AS [t0] ON [t].[TransactionId] = [t0].[Id]
LEFT JOIN [CreditAccounts] AS [c] ON [t0].[CreditAccountId] = [c].[Id]
LEFT JOIN [UserAccount] AS [u] ON [c].[UserAccountId] = [u].[Id]
WHERE [u].[Id] = @__userAccount_Id_0
GROUP BY [c].[ExternalId]
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 1
    That certainly works. So the take away is that the the summing has to be done on the root table, and writing the LINQ needs to take this into account. THanks for the help. – Craig Nov 18 '19 at 00:28