Actual Data
ID | Qty | UnitPrice
1 2 10.00
1 3 30.00
I wanna group by this table with same ID, but it show put two row of output like this
Fail Output
ID | Total
1 20.00
1 90.00
but actually i wish my output is like this
ID | Total
1 110.00
pls kindly advise cause i still new in linq , thank alot
public DataTable GetQuotationInfo(int QuotationId)
{
FADB db = new FADB();
DataTable dt = new DataTable();
dt.Columns.Add("QuotationId");
dt.Columns.Add("ServiceOrderNo");
dt.Columns.Add("Date");
dt.Columns.Add("Name");
dt.Columns.Add("OnwerAddress");
dt.Columns.Add("QuotationNo");
dt.Columns.Add("Premises");
dt.Columns.Add("PayableTo");
dt.Columns.Add("Total");
dt.Columns.Add("Qty");
dt.Columns.Add("UnitNo");
dt.Columns.Add("Status");
dt = (from q in db.Csm_quotations
join j in db.Csm_quotation_jobs on q.QuotationId equals j.QuotationId
join c in db.Csm_complaints on q.ComplaintId equals c.ComplaintID
join f in db.Fa_lots on c.UnitId equals f.UnitId
join o in db.Sys_owners on f.OwnerId equals o.OwnerId
group new { q,j,c,f,o } by new { q.QuotationId,c.ComplaintCode,q.QuotationDate,q.QuotationSubject,q.PayableTo,q.Status,f.OwnerAddress,f.Address,o.OwnerName,j.Qty,j.UnitPrice }
into grp
where grp.Key.QuotationId == Convert.ToUInt16(QuotationId)
select new
{
QuotationId = grp.Key.QuotationId,
ServiceOrderNo = grp.Key.ComplaintCode,
Date = grp.Key.QuotationDate.ToString("dd/MM/yyyy"),
Name = grp.Key.OwnerName,
OnwerAddress =grp.Key.OwnerAddress,
QuotationNo = grp.Key.QuotationSubject,
Premises = grp.Key.Address,
PayableTo = grp.Key.PayableTo,
Total = Convert.ToDouble(grp.Sum(x => (grp.Key.Qty * grp.Key.UnitPrice)))
}).ToDataTable();
return dt;
}