I am still new to asp.net core web api one to many relationships, I have two tables: Loan and loanHistories
Loan Model
public class Loan
{
[Key]
public int LoanID { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
public decimal LoanAmount { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
[Display(Name = "Loan Balance")]
private decimal _LoanBalance;
public decimal LoanBalance
{
get
{
return LoanAmount *interestRate;
}
set { _LoanBalance = value; }
}
[Display(Name = "Interest Rate")]
public decimal interestRate { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Application Date")]
public DateTime ApplicationDate { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Disbursement Date")]
public DateTime DisbursmentDate { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Due Date")]
public DateTime DueDate { get; set; }
[Display(Name = "Defaulted")]
public bool Defaulted { get; set; }
[Display(Name = "Approved")]
public bool Approved { get; set; }
//Navigation property
public int CustomerID { get; set; }
public Customer customer { get; set; }
//public ICollection<LoanComments> loancomments { get; set; }
public ICollection<LoansHistories> loansHistories { get; set; }
}
LoanHistories model:
public class LoansHistories
{
[Key]
public int HistID { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
[Display(Name = "Repaid Amount")]
public decimal RePaidIn { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Repayement Date")]
public DateTime RepayementDateDate { get; set; }
[Display(Name = "No of paying interest only")]
public int NoOfPayinyingIntrestOnly { get; set; }
//Navigation properties
public int LoanID { get; set; }
public Loan loan { get; set; }
}
It generates the following Json data
[{"loanID":1,"loanAmount":1000.0000,"loanBalance":15000.000000,"interestRate":15.00,"applicationDate":"2022-03-28T00:00:00","disbursmentDate":"2022-03-28T00:00:00","dueDate":"2022-04-28T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[{"histID":1,"rePaidIn":500.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":1},{"histID":4,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-21T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":1},{"histID":5,"rePaidIn":50000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":1},{"histID":6,"rePaidIn":50000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":5,"loanID":1},{"histID":7,"rePaidIn":50000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":1},{"histID":8,"rePaidIn":50000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":1},{"histID":10,"rePaidIn":200.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":8,"loanID":1}]},{"loanID":2,"loanAmount":3000.0000,"loanBalance":45000.000000,"interestRate":15.00,"applicationDate":"2022-03-28T00:00:00","disbursmentDate":"2022-03-28T00:00:00","dueDate":"2022-03-28T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[{"histID":2,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-08T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":2},{"histID":9,"rePaidIn":50000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":2,"loanID":2}]},{"loanID":3,"loanAmount":1000.0000,"loanBalance":150.000000,"interestRate":0.15,"applicationDate":"2022-03-28T00:00:00","disbursmentDate":"2022-03-28T00:00:00","dueDate":"2022-03-28T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[]},{"loanID":4,"loanAmount":2000.0000,"loanBalance":2300.000000,"interestRate":1.15,"applicationDate":"2022-03-28T00:00:00","disbursmentDate":"2022-03-28T00:00:00","dueDate":"2022-03-28T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[{"histID":3,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":4},{"histID":11,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":3,"loanID":4},{"histID":12,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":6,"loanID":4},{"histID":13,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":6,"loanID":4},{"histID":14,"rePaidIn":2000.0000,"repayementDateDate":"2022-02-27T00:00:00","noOfPayinyingIntrestOnly":7,"loanID":4},{"histID":15,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":4},{"histID":16,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-14T00:00:00","noOfPayinyingIntrestOnly":6,"loanID":4},{"histID":17,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":3,"loanID":4},{"histID":18,"rePaidIn":2000.0000,"repayementDateDate":"2022-03-22T00:00:00","noOfPayinyingIntrestOnly":8,"loanID":4}]},{"loanID":5,"loanAmount":1000.0000,"loanBalance":1150.000000,"interestRate":1.15,"applicationDate":"2022-03-28T00:00:00","disbursmentDate":"2022-03-29T00:00:00","dueDate":"2021-04-29T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[]},{"loanID":8,"loanAmount":3000.0000,"loanBalance":3450.000000,"interestRate":1.15,"applicationDate":"2022-04-25T00:00:00","disbursmentDate":"2022-03-28T00:00:00","dueDate":"2022-04-26T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[]},{"loanID":9,"loanAmount":1000.0000,"loanBalance":1150.000000,"interestRate":1.15,"applicationDate":"2022-04-13T00:00:00","disbursmentDate":"2022-04-04T00:00:00","dueDate":"2022-04-25T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[]},{"loanID":10,"loanAmount":3000.0000,"loanBalance":3450.000000,"interestRate":1.15,"applicationDate":"2022-04-19T00:00:00","disbursmentDate":"2022-04-26T00:00:00","dueDate":"2022-04-25T00:00:00","defaulted":false,"approved":true,"customerID":40140676,"customer":null,"loansHistories":[{"histID":1012,"rePaidIn":300.0000,"repayementDateDate":"2022-04-05T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":10},{"histID":1013,"rePaidIn":150.0000,"repayementDateDate":"2022-04-19T00:00:00","noOfPayinyingIntrestOnly":2,"loanID":10},{"histID":1014,"rePaidIn":300.0000,"repayementDateDate":"2022-04-11T00:00:00","noOfPayinyingIntrestOnly":3,"loanID":10},{"histID":1015,"rePaidIn":150.0000,"repayementDateDate":"2022-04-11T00:00:00","noOfPayinyingIntrestOnly":4,"loanID":10},{"histID":1016,"rePaidIn":300.0000,"repayementDateDate":"2022-04-06T00:00:00","noOfPayinyingIntrestOnly":5,"loanID":10},{"histID":1017,"rePaidIn":150.0000,"repayementDateDate":"2022-04-04T00:00:00","noOfPayinyingIntrestOnly":6,"loanID":10},{"histID":1018,"rePaidIn":300.0000,"repayementDateDate":"2022-04-05T00:00:00","noOfPayinyingIntrestOnly":7,"loanID":10},{"histID":1019,"rePaidIn":150.0000,"repayementDateDate":"2022-04-19T00:00:00","noOfPayinyingIntrestOnly":8,"loanID":10},{"histID":1020,"rePaidIn":150.0000,"repayementDateDate":"2022-04-02T00:00:00","noOfPayinyingIntrestOnly":9,"loanID":10},{"histID":1021,"rePaidIn":150.0000,"repayementDateDate":"2022-04-04T00:00:00","noOfPayinyingIntrestOnly":10,"loanID":10},{"histID":1022,"rePaidIn":100.0000,"repayementDateDate":"2022-04-06T00:00:00","noOfPayinyingIntrestOnly":12,"loanID":10},{"histID":1023,"rePaidIn":350.0000,"repayementDateDate":"2022-04-04T00:00:00","noOfPayinyingIntrestOnly":13,"loanID":10},{"histID":1024,"rePaidIn":100.0000,"repayementDateDate":"2022-04-12T00:00:00","noOfPayinyingIntrestOnly":14,"loanID":10},{"histID":1025,"rePaidIn":350.0000,"repayementDateDate":"2022-04-11T00:00:00","noOfPayinyingIntrestOnly":6,"loanID":10},{"histID":1026,"rePaidIn":100.0000,"repayementDateDate":"2022-04-20T00:00:00","noOfPayinyingIntrestOnly":14,"loanID":10},{"histID":1027,"rePaidIn":1000.0000,"repayementDateDate":"2022-04-12T00:00:00","noOfPayinyingIntrestOnly":16,"loanID":10},{"histID":1028,"rePaidIn":1000.0000,"repayementDateDate":"2022-04-04T00:00:00","noOfPayinyingIntrestOnly":17,"loanID":10},{"histID":1029,"rePaidIn":1000.0000,"repayementDateDate":"2022-04-04T00:00:00","noOfPayinyingIntrestOnly":18,"loanID":10}]},{"loanID":11,"loanAmount":1000.0000,"loanBalance":1150.000000,"interestRate":1.15,"applicationDate":"2022-04-26T00:00:00","disbursmentDate":"2022-04-25T00:00:00","dueDate":"2022-04-05T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[{"histID":1030,"rePaidIn":150.0000,"repayementDateDate":"2022-04-05T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":11},{"histID":1031,"rePaidIn":150.0000,"repayementDateDate":"2022-04-12T00:00:00","noOfPayinyingIntrestOnly":5,"loanID":11}]},{"loanID":12,"loanAmount":1000.0000,"loanBalance":1150.000000,"interestRate":1.15,"applicationDate":"2022-04-24T00:00:00","disbursmentDate":"2022-04-24T00:00:00","dueDate":"2022-04-25T00:00:00","defaulted":false,"approved":true,"customerID":40140676,"customer":null,"loansHistories":[{"histID":1032,"rePaidIn":150.0000,"repayementDateDate":"2022-04-04T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":12},{"histID":1033,"rePaidIn":150.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":3,"loanID":12}]},{"loanID":13,"loanAmount":2000.0000,"loanBalance":2300.000000,"interestRate":1.15,"applicationDate":"2022-04-24T00:00:00","disbursmentDate":"2022-04-25T00:00:00","dueDate":"2022-05-03T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[{"histID":1034,"rePaidIn":300.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":13},{"histID":1035,"rePaidIn":300.0000,"repayementDateDate":"2022-04-12T00:00:00","noOfPayinyingIntrestOnly":9,"loanID":13}]},{"loanID":14,"loanAmount":1000.0000,"loanBalance":1150.000000,"interestRate":1.15,"applicationDate":"2022-04-25T00:00:00","disbursmentDate":"2022-04-06T00:00:00","dueDate":"2022-04-26T00:00:00","defaulted":false,"approved":true,"customerID":30290122,"customer":null,"loansHistories":[{"histID":1036,"rePaidIn":150.0000,"repayementDateDate":"2022-04-05T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":14},{"histID":1037,"rePaidIn":1000.0000,"repayementDateDate":"2022-04-04T00:00:00","noOfPayinyingIntrestOnly":4,"loanID":14},{"histID":1038,"rePaidIn":150.0000,"repayementDateDate":"2022-04-18T00:00:00","noOfPayinyingIntrestOnly":4,"loanID":14},{"histID":1039,"rePaidIn":1000.0000,"repayementDateDate":"2022-04-11T00:00:00","noOfPayinyingIntrestOnly":5,"loanID":14},{"histID":1040,"rePaidIn":100.0000,"repayementDateDate":"2022-04-19T00:00:00","noOfPayinyingIntrestOnly":6,"loanID":14},{"histID":1041,"rePaidIn":50.0000,"repayementDateDate":"2022-04-04T00:00:00","noOfPayinyingIntrestOnly":7,"loanID":14},{"histID":1042,"rePaidIn":50.0000,"repayementDateDate":"2022-04-05T00:00:00","noOfPayinyingIntrestOnly":8,"loanID":14},{"histID":1043,"rePaidIn":50.0000,"repayementDateDate":"2022-04-06T00:00:00","noOfPayinyingIntrestOnly":8,"loanID":14},{"histID":1044,"rePaidIn":1000.0000,"repayementDateDate":"2022-03-28T00:00:00","noOfPayinyingIntrestOnly":89,"loanID":14}]},{"loanID":1014,"loanAmount":5000.0000,"loanBalance":5750.000000,"interestRate":1.15,"applicationDate":"2022-03-28T00:00:00","disbursmentDate":"2022-04-11T00:00:00","dueDate":"2022-04-12T00:00:00","defaulted":false,"approved":true,"customerID":40140676,"customer":null,"loansHistories":[{"histID":2035,"rePaidIn":750.0000,"repayementDateDate":"2022-04-11T00:00:00","noOfPayinyingIntrestOnly":1,"loanID":1014},{"histID":2036,"rePaidIn":1000.0000,"repayementDateDate":"2022-04-12T00:00:00","noOfPayinyingIntrestOnly":2,"loanID":1014},{"histID":2037,"rePaidIn":750.0000,"repayementDateDate":"2022-03-30T00:00:00","noOfPayinyingIntrestOnly":3,"loanID":1014}]}]
Now I want to calculate the total sum of rePaidIn within loanHistories table and display in loan table Has TotalRepaidIn with heading [NotMapped]: I have tried from the following link .it seems close to answer my question but it does not Here is my Controller
[HttpGet]
public async Task<ActionResult<IEnumerable<Loan>>> Getloans()
{
return await _context.loans.Include(lh => lh.loansHistories).ToListAsync();
}
Any solution will be highly appreciated. Thanks in advance
Here is a whole working demo:
Model:
public class Loan
{
[Key]
public int LoanID { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
public decimal LoanAmount { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
[Display(Name = "Loan Balance")]
private decimal _LoanBalance;
[Column(TypeName = "decimal(18,2)")]
public decimal LoanBalance
{
get
{
return LoanAmount * interestRate;
}
set { _LoanBalance = value; }
}
[Column(TypeName = "decimal(18,2)")]
[Display(Name = "Interest Rate")]
public decimal interestRate { get; set; }
//....
[Display(Name = "Approved")]
public bool Approved { get; set; }
//public ICollection<LoanComments> loancomments { get; set; }
public ICollection<LoansHistories> loansHistories { get; set; }
[NotMapped]
public decimal TotalRepaidIn { get; set; } //add this...
}
public class LoansHistories
{
[Key]
public int HistID { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
[Display(Name = "Repaid Amount")]
public decimal RePaidIn { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Repayement Date")]
public DateTime RepayementDateDate { get; set; }
[Display(Name = "No of paying interest only")]
public int NoOfPayinyingIntrestOnly { get; set; }
public int LoanID { get; set; }
public Loan loan { get; set; }
}
Controller:
public async Task<ActionResult<IEnumerable<Loan>>> Getloans()
{
var data = await _context.loans.Include(lh => lh.loansHistories).Select(l=>new Loan()
{
TotalRepaidIn = l.loansHistories.Select(lh=>lh.RePaidIn).Sum(),
loansHistories = l.loansHistories,
ApplicationDate = l.ApplicationDate,
Defaulted=l.Defaulted,
DisbursmentDate= l.DisbursmentDate,
DueDate= l.DueDate,
LoanID=l.LoanID,
Approved=l.Approved,
interestRate=l.interestRate,
LoanAmount=l.LoanAmount
}).ToListAsync();
return data;
}
Update:
A better way is to change your model like below:
using System.Linq; //import this namespace...
public class Loan
{
[Key]
public int LoanID { get; set; }
[DataType(DataType.Currency)]
[Column(TypeName = "money")]
public decimal LoanAmount { get; set; }
private decimal _TotalRepaidIn;
[NotMapped]
public decimal TotalRepaidIn
{
get { return loansHistories.Sum(sum => sum.RePaidIn); }
set { _TotalRepaidIn = value; }
}
//other properties
public ICollection<LoansHistories> loansHistories { get; set; } = new List<LoansHistories>();
}
Controller:
public async Task<ActionResult<IEnumerable<Loan>>> Getloans()
{
return await _context.loans.Include(lh => lh.loansHistories).ToListAsync();
}