var result = from p in db.Products.Where(p => p.IsInActive == false && p.IsLiterature == false)
join up in db.UserToProducts.Where(up => up.IsInActive == false
&& up.UserID == userID) on p.ProductID equals up.ProductID
join ul in db.UserToLocations.Where(ul => ul.IsInActive == false)
on up.UserID equals ul.UserID
join l in db.Locations.Where(l => l.IsInActive == false)
on ul.LocationID equals l.LocationID
join s in db.Sales.Where(s => s.IsInActive == false && s.UserID == userID && s.Date.Month == month
&& s.Date.Year == year && s.WholeSalerID == wholeSalerId) on l.LocationID equals s.LocationID into joined
from merged in joined.DefaultIfEmpty()
group new { up.Product, ul.Location, merged }
by new
{
LocationID = ul.Location.LocationID,
ul.Location.Name,
ProductID = up.Product.ProductID,
ProdictName = up.Product.Name,
up.Product.Code,
month = merged.Date.Month,
year = merged.Date.Year,
merged.Date
} into g
select new SalesachievementBO()
{
ProductID = g.Key.ProductID,
ProductName = g.Key.ProdictName,
ProductCode = g.Key.Code,
LocationId = g.Key.LocationID,
LocationName = g.Key.Name,
Qty = g.Sum(p => (p.merged.Qty == null ? 0 : p.merged.Qty)),
Price = g.Sum(p => p.merged.Price == null ? 0 : p.merged.Price)
};