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();
CodePudding user response:
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);
}