c# LINQ表达式在求和组连接时不能翻译

Question

我有两个表:users和transactions。事务表存储用户钱包事务(+ balance， - balance)等。我试图获得基于他们的钱包的当前余额按最大的第一排序的用户列表。(我可以通过将Transactions表中的Amount列相加来获得总余额)。

var query = from u in _context.users
join t in _context.Transactions on u.id equals t.UserId into gj
from txn in gj.DefaultIfEmpty()
where txn.TransactionStatus == Transaction.Status.Success && !u.Deleted.HasValue
select new
{
balance = gj.Sum(a => a.Amount),
user = u,
};
var result = await query.OrderByDescending(t => t.balance).Skip(offset).Take(range).ToListAsync();

我得到错误:

LINQ表达式'gj'无法翻译。

这是我想要实现的等效SQL:

SELECT balance, u.* FROM
(SELECT COALESCE(SUM(Amount), 0) as balance, u.id
FROM dbo.users u
LEFT JOIN dbo.Transactions t ON(u.id = t.UserId AND t.TransactionStatus = 0)
WHERE u.Deleted IS NULL
GROUP BY u.id) as tbl
JOIN dbo.users u ON(u.id = tbl.id)
ORDER BY balance DESC

Answer 1

从用户开始，使用导航道具到Trans应该会使这个问题变得简单。试试这样的东西:

users.Where(u => u.Deleted == null)
.Select(u => new {
User = u,
Balance = u.Transactions.Where(t => t.Status == 0).Sum(t => t.Amount)
})
.OrderByDescending(at => at.Balance);

如果逻辑真的是"列出所有用户，但只添加未删除用户的事务，并将删除的用户显示为0 balance";然后:

users.Where(u => u.Deleted == null)
.Select(u => new {
User = u,
Balance = u.Deleted != null ? 0 : u.Transactions.Where(t => t.Status == 0).Sum(t => t.Amount)
})
.OrderByDescending(at => at.Balance);

尽量不要写EF查询像"右，在SQL中，我会有这个表和连接那个表，这将是一个左连接，这将被求和…我会告诉EF用这个上下文集连接那个上下文集，这是一个左连接，和。"EF会帮你写join用你希望如何操作c#对象图的方式来表达你的需求，并让EF尽其所能地转换为SQL;在实体之间使用导航道具，这样它就可以计算出您希望如何将数据放在一起并安排必要的连接。它很少需要以sql风格的方式进行微管理