The following query I wrote in SQL Management Studio and it works fine
select distinct location.LocationId, location.Name, location.WorkArea,
crop.CropId, HubCropTypes.Name, crop.recommendedDate, activity.ApplicationDate, activity.Type, crop.IsDeleted cropDeleted, activity.IsDeleted activityDeleted
from location
left outer join crop on location.LocationId = crop.LocationId
left outer join activity on crop.CropId = Activity.CropID
left outer join HubCropTypes on crop.HubCropTypeID = HubCropTypes.HubCropTypeID
where location.FarmId = 'xxxxxxxxx'
and location.IsDeleted = 0
order by location.LocationId,crop.recommendedDate, location.Name
However, the LINQ version does not, and after playing around in SQL I found it's down to the left joins. I thought DefaultIfEmpty replaces left join in LINQ?
var query = (from l in _db.Locations
join c in _db.Crops.DefaultIfEmpty() on l.LocationId equals c.LocationId
join a in _db.Activities.DefaultIfEmpty() on c.CropId equals a.CropID
join h in _db.HubCropTypes.DefaultIfEmpty() on c.HubCropTypeID equals h.HubCropTypeID
where l.FarmId == FarmID
where l.IsDeleted == false
select new
{
LocationID = l.LocationId,
LocationName = l.Name,
WorkArea = l.WorkArea,
CropID = c.CropId,
CropName = h.Name,
CropRecommendedDate = c.RecommendedDate,
ActivityApplicationDate = a.ApplicationDate,
ActivityType = a.Type,
isCropDeleted = c.IsDeleted,
isActivityDeleted = a.IsDeleted
}).Distinct();