//For LOV in Grid
public List <PRDChemProdReqItem> GetStoreWiseChemicalItemStock(string _IssueFrom)
{
using (_context)
{
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()
}).ToList();
var Data = (from s in StockResult
where s.ClosingQty > 0
join i in _context.Sys_ChemicalItem on(s == null ? null : s.ItemID) equals i.ItemID into Items
from i in Items.DefaultIfEmpty()
join sup in _context.Sys_Supplier on(s == null ? 0 : s.SupplierID) equals sup.SupplierID into Suppliers
from sup in Suppliers.DefaultIfEmpty()
join si in _context.Sys_Size on(s == null ? 0 : s.PackSize) equals si.SizeID into Sizes
from si in Sizes.DefaultIfEmpty()
join siu in _context.Sys_Unit on(s == null ? 0 : s.SizeUnit) equals siu.UnitID into Units
from siu in Units.DefaultIfEmpty()
join su in _context.Sys_Unit on(s == null ? 0 : s.StockUnit) equals su.UnitID into StockUnits
from su in StockUnits.DefaultIfEmpty()
orderby(i == null ? null : i.ItemName)
select new PRDChemProdReqItem
{
ItemID = s.ItemID,
ItemName = (i == null ? null : i.ItemName),
SupplierID = s.SupplierID,
SupplierName = (sup == null ? null : sup.SupplierName),
PackSize = s.PackSize,
PackSizeName = (si == null ? null : si.SizeName),
SizeUnit = s.SizeUnit,
SizeUnitName = (siu == null ? null : siu.UnitName),
StockQty = s.ClosingQty,
StockUnit = s.StockUnit,
StockUnitName = (su == null ? null : su.UnitName),
PackQty = (Int32)(s.PackQty)
}).ToList();
return(Data);
}
}