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 AmountClaimed from 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();