如何從僅共享一個外鍵的兩個表中減去兩列到另一個表

我有 3 個表如下：

1. 銀行資訊

public class Bank 
{
    [Key]
    public int ID {get; set;}

    [Required(ErrorMessage ="Bank Name is required")]
    [StringLength(100)]
    public string BankName { get; set; }

    [Required(ErrorMessage = "Bank Name is required")]
    [StringLength(100)]
    public string AccountNo { get; set; }
}

2. 費用明細

public class Expense 
{
    [Key]
    public int ID {get; set;}

    public int SubAccountID { get; set; }
    [ForeignKey(nameof(SubAccountID))]
    public virtual SubAccount SubAccount { get; set; }

    public int MonthID { get; set; }
    [ForeignKey(nameof(MonthID))]
    public virtual Month Month { get; set; }

    public int Year { get; set; }
    public DateTime ReceiptDate { get; set; }
    [Display(Name = "Beneficiary")]
    [StringLength(100)]
    [Required(ErrorMessage = "Beneficiary is required")]
    public string Beneficiary { get; set; }
    [Required(ErrorMessage = "Ammount is required")]
    public double ReceiptAmount { get; set; }

    [Display(Name = "Description")]
    [StringLength(300)]
    public string Description { get; set; }

    [Required(ErrorMessage ="Bank is required")]
    [Display(Name ="Bank")]
    public int BanksID { get; set; }
    [ForeignKey(nameof(BanksID))]
    public virtual Bank Bank { get; set; }
}

3. 收入詳情

public class IncomeTransaction         
{
    [Key]
    public int ID { get; set; }

    [Required(ErrorMessage ="Bank Info is required")]

    public int BankID { get; set; }
    [ForeignKey(nameof(BankID))]
    public Bank Bank { get; set; }

    [Display(Name ="Transaction No")]
    [StringLength(100)]
    public string TransactionNo { get; set; }

    [Required(ErrorMessage = "Amount is required")]
    public double Amount { get; set; }

    [Required(ErrorMessage = "Transaction Date is required")]
    [Display(Name = "Transaction Date")]
    public DateTime TransactionDate { get; set; }

    [Required(ErrorMessage ="Income Source is required")]
    [StringLength(100)] 
    [Display(Name ="Income Source")]
    public string IncomeSource { get; set; }
}

我還有一個視圖模型來顯示銀行詳細資訊和當前余額

4.共享銀行模式

public class SharedBank 
{
    public int ID {get; set;}
    [Required(ErrorMessage = "Bank Name is required")]
    [StringLength(100)]
    public string BankName { get; set; }

    [Required(ErrorMessage = "Bank Name is required")]
    [StringLength(100)]
    public string AccountNo { get; set; }

    [NotMapped]
    public double Balance { get; set; }
}

我想回傳每家銀行的銀行資訊和當前余額。到目前為止，我已經嘗試過：

public async Task<IEnumerable<SharedBank>> GetBankBalances()
{
    return await (
        from bank in _pisa.Banks
        join trans in _pisa.IncomeTransactions on bank.ID equals trans.BankID
        join exp in _pisa.Expenses on bank.ID equals exp.BanksID
        group new {trans,exp, bank} by new {trans.BankID, exp.BanksID, bank.ID,bank.BankName,bank.AccountNo} into g
        select new SharedBank()
        {
            ID = g.Key.ID,
            AccountNo = g.Key.AccountNo,
            BankName = g.Key.BankName,
            Balance = g.Sum(o=> o.trans.Amount - o.exp.ReceiptAmount )

        })
    .Distinct()
    .ToListAsync();          
}

此查詢將收入金額乘以費用記錄數，就像我的收入交易表中有 1 條金額為 2000 的記錄，我也有 3 條費用記錄，所以此查詢給我的收入金額為 6000 而不是 2000，我想將每家銀行的總費用減去總收入作為余額

請幫忙，我怎樣才能做到這一點。非常感謝您

[Bank Table][1]

[收入交易][2]

[Expennses Table][3]

[頁面][4]

  [1]: https://i.stack.imgur.com/8IHwV.png
  [2]: https://i.stack.imgur.com/K5lYH.png
  [3]: https://i.stack.imgur.com/D4aIU.png
  [4]: https://i.stack.imgur.com/wEvxB.png

uj5u.com熱心網友回復：

在“銀行”內添加兩個導航屬性

    public List<IncomeTransaction> IncomeTransactions { get; set; }
    public List<Expense> Expenses { get; set; }

然后，不要使用“加入”，而是使用“包含”：

    var result = banks.Include(x=>x.Expenses)
                      .Include(x=>x.IncomeTransactions)
                      .Select(x => new SharedBank
                       {
                         Balance = x.Expenses.Sum(y => y.ReceiptAmount) - x.IncomeTransactions.Sum(x => x.Amount)
                       });

uj5u.com熱心網友回復：

看起來您需要分別添加所有收入和所有費用，然后減去總計以獲得最終余額。像這樣：

var bankBalances = banks
    .Join(expenses, 
          b => b.ID, 
          e => e.BanksID, 
          (b, e) => new { Bank = b, Expense = e})
    .Join(transactions, 
          x => x.Bank.ID, 
          t => t.BankID, 
          (x, t) => new { Bank = x.Bank, Expense = x.Expense, Transaction = t })
    .GroupBy(x => new { x.Bank.ID, x.Bank.BankName, x.Bank.AccountNo } )
    .Select(x => new SharedBank 
    { 
        ID = x.Key.ID, 
        BankName = x.Key.BankName, 
        AccountNo = x.Key.AccountNo, 
        Balance = x.Sum(t => t.Transaction.Amount) - x.Sum(e => e.Expense.ReceiptAmount)
    });
    

uj5u.com熱心網友回復：

生成的查詢 感謝 Carlo Bos 為我指明了正確的方向。我不能用 linq 查詢來做到這一點，但我已經用存盤程序解決了我的問題：

select ID, BankName,AccountNo ,(select isnull(sum(Amount),0) from IncomeTransactions where BankID=banks.ID)Income ,(select isnull(sum(ReceiptAmount),0) from Expenses where BanksID =banks.ID)Expenses ,(select isnull(sum(Amount),0) from IncomeTransactions where BankID=banks.ID)- (select isnull(sum(ReceiptAmount),0) from Expenses where BanksID =banks.ID)Balance from Banks

標籤：林克

上一篇：部分匹配LINQ查詢

下一篇：GroupBy無法從用法推斷型別引數