如何使用LINQ查询计算指定银行账户的余额？

我可以通过SQL查询计算银行账户的余额，但无法写出对应的LINQ查询，求帮助。相关实体类与SQL查询如下：

实体类代码 #

public class tbl_BankName
{
    [Key]
    public int bankid { get; set; }
    public string BankName { get; set; }
    public string AccountNo { get; set; }
}

public class tbl_bankTransaction
{
    [Key]
    public int bankTransctionid { get; set; }
    public int bankid { get; set; }
    public decimal? debited { get; set; }
    public decimal? credited { get; set; }
}

public class bankvm
{
    public int bankid { get; set; }
    public string BankName { get; set; }
    public string AccountNo { get; set; }
    public decimal? debited { get; set; }
    public decimal? credited { get; set; }
    public decimal? balance { get; set; }
}

原SQL查询 #

SELECT
    b.bankid, c.BankName, c.AccountNo, b.Balance
FROM
    (SELECT
         a.bankid, 
         Balance = SUM(ISNULL(a.credited, 0)) - SUM(ISNULL(a.debited, 0))
     FROM 
         tbl_BankTransactions a
     GROUP BY
         a.bankid 
     HAVING
         SUM(ISNULL(a.credited, 0)) - SUM(ISNULL(a.debited, 0)) <> 0) b
JOIN
    tbl_BankNames c ON c.bankid = b.bankid
WHERE
    b.bankid = 2 

对应的LINQ查询（EF Core） #

以下是与原SQL逻辑完全对应的LINQ查询，适用于EF Core等ORM框架（会转换为SQL执行）：

var result = (from transaction in _context.tbl_bankTransaction
              group transaction by transaction.bankid into bankGroup
              let totalCredited = bankGroup.Sum(t => t.credited ?? 0)
              let totalDebited = bankGroup.Sum(t => t.debited ?? 0)
              let balance = totalCredited - totalDebited
              where balance != 0 && bankGroup.Key == 2
              join bank in _context.tbl_BankName on bankGroup.Key equals bank.bankid
              select new bankvm
              {
                  bankid = bank.bankid,
                  BankName = bank.BankName,
                  AccountNo = bank.AccountNo,
                  balance = balance
              }).FirstOrDefault();

逻辑说明 #

1. 分组：按bankid对交易记录分组，对应SQL中的GROUP BY a.bankid
2. 计算总额：用?? 0处理可空值，对应SQL的ISNULL，分别计算总贷记和总借记
3. 筛选非零余额：通过where balance != 0实现SQL的HAVING条件
4. 关联银行信息：将分组结果与tbl_BankName关联，对应SQL的JOIN
5. 筛选指定账户：bankGroup.Key == 2对应SQL的WHERE b.bankid = 2
6. 投影到ViewModel：将结果映射到bankvm类

如果是处理内存中的集合（LINQ to Objects），代码逻辑一致，只需将_context替换为对应的集合变量即可。

内容的提问来源于stack exchange，提问作者Jewelserniabad