Loading data from three related tables in ASP.NET Core using Entity Framwork Core in one-to-many rel-CodePudding

I have three tables Customer, Loan and LoanHistories. Customer table is related to Loan table in a one-to-many relationship, and the Loan table is related to LoanHistories in one-to-many relationship as well.

The following are the C# classes for these tables:

public class Customer
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Display(Name = "ID NO")]
    public int CustomerID { get; set; }
    [Display(Name = "First Name")]
    public string FirstName { get; set; }
    [Display(Name = "Last Name")]
    public string LastName { get; set; }
    [Display(Name = "Phone Number")]
    public string PhoneNo { get; set; }
    [Display(Name = "Nearest Primary School")]
    public string NearestPrimarySchool { get; set; }
    [Display(Name = "Photo")]
    public string Photo { get; set; }

    public ICollection<Loan> loans { get; set; }
    // public IEnumerable<LoansHistories> loansHistories { get; set; }
   // public ICollection<WorkForLiving> workForLivings { get; set; }
   // public ICollection<CustomersCharacterBehavior> customersCharacterBehaviors { get; set; }
}

public class Loan
{
    [Key]
    public int LoanID { get; set; }
    [DataType(DataType.Currency)]
    [Column(TypeName = "money")]
    public decimal LoanAmount { get; set; }
    [NotMapped]
    public decimal TotalRepaidIn { get; set; }
    [NotMapped]
    public decimal Balance { 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; }
}

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

    // Navigation property
    // public int CustomerID { get; set; }
    // public Customer customer { get; set; }
}

This is the LoansController that loads data from Loans and LoansHistories tables:

[HttpGet]
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(),
            Balance = l.loansHistories.Select(lh => lh.RePaidIn).Sum()-l.LoanAmount,
            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;
}

This is the data in JSON format:

[
   {
      "loanID":1,
      "loanAmount":1000.0000,
      "totalRepaidIn":202700.0000,
      "balance":201700.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":0,
      "customer":null,
      "loansHistories":[
         {
            "histID":1,
            "rePaidIn":500.0000,
            "repayementDateDate":"2022-03-28T00:00:00",
            "noOfPayinyingIntrestOnly":1,
            "loanID":1,
            "loan":null
         }
      ]
   }
]

I want to load data from customer controller but I found entity TotalRepaidIn to be Zero, How can I make it return a value has its returning in loan controller? without having entity totalRepaidIn":0.0

Customer controller:

  // GET: api/CustomersApi
    [HttpGet]
    public async Task<ActionResult<IEnumerable<Customer>>> Getcustomers()
    {
        return await _context.customers.Include(l=>l.loans).ThenInclude(h=>h.loansHistories).ToListAsync();
    }

It returns the following Json data:

[
   {
      "customerID":30290122,
      "firstName":"Isaac",
      "lastName":"Kiplagat",
      "phoneNo":"0724797768",
      "nearestPrimarySchool":"Mokwo",
      "photo":"photo",
      "loans":[
         {
            "loanID":1,
            "loanAmount":1000.0000,
            "totalRepaidIn":0.0,
            "balance":0.0,
            "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,
            "loansHistories":[
               {
                  "histID":1,
                  "rePaidIn":500.0000,
                  "repayementDateDate":"2022-03-28T00:00:00",
                  "noOfPayinyingIntrestOnly":1,
                  "loanID":1
               }
            ]
         }
      ]
   }
]

CodePudding user response：

One way is like the previous did you can select the new Customer instance and set value for it one by one.

Another easier way, you can change your property to below to calculate the RePaidIn:

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; }
    [NotMapped]
    public decimal TotalRepaidIn => loansHistories.Sum(sum => sum.RePaidIn);  //change here....
    //other properties.....
    public ICollection<LoansHistories> loansHistories { get; set; }
}

And in your LoansController, you can improve the action to below:

var data = await _context.loans.Include(lh => lh.loansHistories).ToListAsync();

Customer Controller can also get the correct value for TotalRepaidIn.