am trying to convert below query in entity framework core but not able to access field in select query after apply group by.
Select ProdCatg.Category As [Category] ,Prod.FKProdCatgID As [FKProdCatgID] ,SUM(Dtl.Qty + LD.ProdConv1 + Trn.CashAmt ) As [DistributionRateValue] From tblSalesInv_Dtl Dtl Left Join tblSalesInv_Trn Trn on Trn.PKID=Dtl.FKID And Trn.FKSeriesID=Dtl.FKSeriesID Left Join tblProdLot_Dtl LD on Dtl.FKLotID=LD.PKLotID And LD.FKProdID=Dtl.FKProdID Left Join tblProd_Mas Prod ON PKProdID=Dtl.FKProdID Left Join tblProdCatg_Mas ProdCatg On PKProdCatgID=Prod.FKProdCatgID Where Trn.DraftMode=0 Group By Prod.FKProdCatgID,ProdCatg.Category Order By Category
I am trying convert like this but but not able to access Dtl.Qty + LD.ProdConv1 + Trn.CashAmtfields
var result1 = (from dtl in _context.TblSalesInvDtl join ser in lstSeries on dtl.FkseriesId equals ser join trn in _context.TblSalesInvTrn on new { s1 = dtl.Fkid, s2 = ser } equals new { s1 = trn.Pkid, s2 = trn.FkseriesId } join lot in _context.TblProdLotDtl on new { s1 = dtl.FklotId, s2 = dtl.FkprodId } equals new { s1 = lot.PklotId, s2 = lot.FkprodId } join p in _context.TblProdMas on dtl.FkprodId equals p.PkprodId into pr from Prod in pr.DefaultIfEmpty() join pl in _context.TblProdMas on dtl.FklinkedProdId equals pl.PkprodId into plid from ProdLinked in plid.DefaultIfEmpty() join t in _context.TblTaxMas on dtl.FktaxId equals t.PktaxId into tid from Tax in tid.DefaultIfEmpty() join c in _context.TblProdCatgMas on Prod.FkprodCatgId equals c.PkprodCatgId into cid from Catg in cid.DefaultIfEmpty() where trn.EntryDate == Convert.ToDateTime("1-12-2018") group Catg by new { Prod.FkprodCatgId, Catg.Category } into gb //, dtl, lot, Prod select new { CategoryName = gb.FirstOrDefault().Category, DistributionRateValue = gb.Sum(a => (gb.Key.dtl.Qty + gb.Key.lot.ProdConv1+ gb.Key.lot.CashAmt)), FKProdCatgID= gb.Key.Prod.FkprodCatgId, } ).ToList();
can anybody help me how can I access multiple table fields in sum with groupby in entity framework core.