Get the Total and pass to View Model

Question

In my ASP.NET MVC application, I want to create a view of request details and the Total Amount that each request has.

This is how I create my View Model

public class ApprovedMedicals
    {
        public int ReqId { get; set; }
        public int  EmpNo { get; set; }
        public string Company { get; set; }
        public string EmpName { get; set; }
        public string ApprovedMedicalNo { get; set; }
        public decimal TotalAmount { get; set; }
        public DateTime CreatedDate { get; set; }
        public DateTime? ApprovedDate { get; set; }
    }  

To get and assign the data to this I'm using 3 database tables. AppRequest is the main Medical is connected via Foreign key of AppRequest and MedicalExpences Connected via Medical. So one request may have 1 or more records in MedicalExpences table. So I want to get the sum of the AmountClaimedfrom that table and assign it to the Total Amount View Model property. I'm Stuck within how to calculate the sum and assign it to the column part. Can you help me here, please?

List < ApprovedMedicals > report = (from a in db.AppRequest 
                                    join m in db.Medical on a.Id equals m.Req_Id 
                                    join me in db.MedicalExpences on m.Id equals me.Medic_Id 
                                    join e in db.CreateEmployee on a.Create_By equals e.Id 
                                    join c in db.CreateCompany on e.CompanyId equals c.Id 
                                    where m.HodApproval == true && m.HodApprovedDate <= 
                                    ToDate && m.HodApprovedDate >= FromDate

                                    select new ApprovedMedicals {
                                    EmpNo = e.EmpNo,
                                    EmpName = e.EmpName,
                                    Company = c.CompanyName,
                                    ApprovedMedicalNo = m.ApproveNumber,
                                    ApprovedDate = m.HodApprovedDate,
                                    CreatedDate = a.Created_Date,
                                    ReqId = a.Id,
                                    TotalAmount = // Here I want to get the Total of me.Amount

                                    }).OrderByDescending(x => x.ReqId).ToList();

Answer 1

Try :

List < ApprovedMedicals > report = (from a in db.AppRequest 
                                    join m in db.Medical on a.Id equals m.Req_Id 
                                    join me in db.MedicalExpences on m.Id equals me.Medic_Id 
                                    join e in db.CreateEmployee on a.Create_By equals e.Id 
                                    join c in db.CreateCompany on e.CompanyId equals c.Id 
                                    where m.HodApproval == true && m.HodApprovedDate <= 
                                    ToDate && m.HodApprovedDate >= FromDate

                                    select new ApprovedMedicals {
                                    EmpNo = e.EmpNo,
                                    EmpName = e.EmpName,
                                    Company = c.CompanyName,
                                    ApprovedMedicalNo = m.ApproveNumber,
                                    ApprovedDate = m.HodApprovedDate,
                                    CreatedDate = a.Created_Date,
                                    ReqId = a.Id,
                                    TotalAmount = 0

                                    }).OrderByDescending(x => x.ReqId).ToList();


foreach (var item in report)
{
    var Id = db.Medical.Where(x => x.Req_Id == item.ReqId).Select(x => x.Id).FirstOrDefault();
    item.TotalAmount = db.MedicalExpences.Where(x => x.Medic_Id == Id).Sum(x => x.Amount);
}