I Have 3 Tables as following:

1. Bank Info

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. Expense Details

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. Income Details

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; }
}

Also I have a view model to display the bank details and Current Balance

4.Shared BankModel

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; }
}

I want to return the Bank Information and Current Balance for each bank. so far I've tried this :

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

this query multiplies the incomeAmount by Expense record count, like I've 1 record of amount 2000 in my IncomeTransaction table, also I've 3 records in expense, so this query gives me an income amount of 6000 instead of 2000, I want to get the total expense minus total income for each bank as balance

Please help, how can I achieve this. thank you very much

[Bank Table][1]

[IncomeTransactions][2]

[Expennses Table][3]

[The Page][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

CodePudding user response：

add two navigation properties inside "Bank"

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

Then, instead of using "Join", use "Include":

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

CodePudding user response：

It looks like you need to add all income and all expenses separately, then subtract the totals to get the final balance. Like so:

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

CodePudding user response：

generated query thanks Carlo Bos for pointing me the right direction. I couldn't do it with linq query but I've solved my problem with stored procedure:

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