EF Core LINQ查询由于限制而失败?

本文介绍了EF Core LINQ查询由于限制而失败?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试对EF Core 3.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:'处理LINQ表达式'AsQueryable(((未处理的参数:y).TransactionLines)"，通过"NavigationExpandingExpressionVisitor"失败的.这可能表示EF Core中存在错误或局限性.

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();

我正在努力寻找可能出现问题的地方，所以甚至不确定从哪里开始.我正在尝试获取所有交易金额的总和(这是每笔交易的所有TransactionLines的总和-即交易金额由与其相关的行组成).

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).

然后我总结所有交易，然后按CreditAccount ID分组.

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

未处理的参数y 这行令人担忧.也许我的分组和汇总已结束.

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

推荐答案

所以从 TransactionLines 级别开始，这很简单:

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();

(您不需要任何 Include()，因为您没有返回带有相关数据的实体.您正在投影自定义数据形状.)

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

翻译为:

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]