0

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();
DarkW1nter
  • 2,933
  • 11
  • 67
  • 120

0 Answers0