//after Save For Grid Data public List <PRDChemProdReqItem> GetTransactionItemList(long _TransactionID, string _IssueFrom) { using (var context = new BLC_DEVEntities()) { var StockResult = (from p in context.INV_ChemStockSupplier where p.StoreID.ToString() == _IssueFrom group p by new { //p.StoreID, p.SupplierID, p.PackSize, p.ItemID, p.SizeUnit, p.UnitID } into g select new { TransectionID = g.Max(p => p.TransectionID), //StoreID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.StoreID).FirstOrDefault(), ItemID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.ItemID).FirstOrDefault(), SupplierID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.SupplierID).FirstOrDefault(), //UnitID = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.UnitID).FirstOrDefault(), PackSize = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.PackSize).FirstOrDefault(), SizeUnit = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.SizeUnit).FirstOrDefault(), PackQty = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.PackClosingQty).FirstOrDefault(), ClosingQty = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.ClosingQty).FirstOrDefault(), StockUnit = g.Where(p => p.TransectionID == g.Max(q => q.TransectionID)).Select(x => x.UnitID).FirstOrDefault() }); var Data = (from i in context.INV_StoreTransItem where i.TransactionID == _TransactionID // Stock Size from s in StockResult.Where(x => x.ItemID == i.ItemID && x.PackSize == i.PackSize && x.SizeUnit == i.SizeUnit && x.SupplierID == i.SupplierID).DefaultIfEmpty() join si in context.Sys_Size on(s == null ? null : s.PackSize) equals si.SizeID into StockSize from si in StockSize.DefaultIfEmpty() join siu in context.Sys_Unit on(s == null ? null : s.SizeUnit) equals siu.UnitID into StockSizeUnit from siu in StockSizeUnit.DefaultIfEmpty() join su in context.Sys_Unit on(s == null ? null : s.StockUnit) equals su.UnitID into StockUnit from su in StockUnit.DefaultIfEmpty() // Stock Size join it in context.Sys_ChemicalItem on(i == null ? null : i.ItemID) equals it.ItemID into Chemicals from it in Chemicals.DefaultIfEmpty() join sup in context.Sys_Supplier on(i == null ? null : i.SupplierID) equals sup.SupplierID into Suppliers from sup in Suppliers.DefaultIfEmpty() join iu in context.Sys_Unit on(i == null ? null : i.TransactionUnit) equals iu.UnitID into IUnits from iu in IUnits.DefaultIfEmpty() join issuesize in context.Sys_Size on(i == null ? null : i.PackSize) equals issuesize.SizeID into IssueSizes from issuesize in IssueSizes.DefaultIfEmpty() join issuesizeunit in context.Sys_Unit on(i == null ? null : i.SizeUnit) equals issuesizeunit.UnitID into IssueSizeUnit from issuesizeunit in IssueSizeUnit.DefaultIfEmpty() orderby(it == null ? null : it.ItemName) select new PRDChemProdReqItem { TransItemID = i.TransItemID, ItemID = i.ItemID, ItemName = (it == null ? null : it.ItemName), SupplierID = i.SupplierID, SupplierName = (sup == null ? null : sup.SupplierName), PackSize = (s == null ? 0 : s.PackSize), PackSizeName = (si == null ? null : si.SizeName), SizeUnit = (s == null ? 0 : s.SizeUnit), SizeUnitName = (siu == null ? null : siu.UnitName), PackQty = (s == null ? 0 : (Int32)(s.PackQty)), StockQty = (s == null ? 0 : s.ClosingQty), StockUnit = (s == null ? 0 : s.StockUnit), StockUnitName = (su == null ? null : su.UnitName), IssuePackSize = i.PackSize, IssuePackSizeName = (issuesize == null ? null : issuesize.SizeName), IssueSizeUnit = i.SizeUnit, IssueSizeUnitName = (issuesizeunit == null ? null : issuesizeunit.UnitName), IssuePackQty = i.PackQty, IssueQty = i.TransactionQty, IssueUnitName = (iu == null ? null : iu.UnitName) }).ToList(); return(Data); } }