0

I have an issue at the moment where I'm trying to join two tables and then select records however if the join fails then this means that the records where the join failed are omitted from the results. I'd still like them to be included and maybe just enter some default value in the one property that's used from the join.

My current query is:

`

var query = _context.spl.Where(x => x.Active == true)
                .Join(_context.GeographyPostCodesGeolocations, spl => spl.ServiceProviderLocPostcode1 + " " + spl.ServiceProviderLocPostcode2, pc => pc.PostCode, (spl, pc) => new
                {
                    serviceProviderLocation = spl,
                    postCodeData = pc,
                    distanceFromLoc = postCodeGeoObj != null ? Math.Sqrt(Math.Pow((pc.Eastings - postCodeGeoObj.Eastings), 2) + Math.Pow((pc.Northings - postCodeGeoObj.Northings), 2)) * 0.000621 : 0
                })
                .Select(x => new ServiceProviderLocationListViewModel
                {
                    DistanceFromVehicleLoc = x.distanceFromLoc,
                    Eastings = x.postCodeData.Eastings,
                    Northings = x.postCodeData.Northings,
                    ServiceProviderLocation = x.serviceProviderLocation.serviceProviderLocName
                })
                .AsQueryable();

`

As you can see at the moment it's not possible for the .select to work unless the join works as the objects in there require the join to have worked.

Is there some way to catch any items that don't work within the .join? I found some info on DefaultIfEmpty but I couldn't seem to get that to work. The other tricky bit is that this is a queryable as there are filters applied afterwards when querying.

CodeFox
  • 1
  • Use [`GroupJoin`](https://stackoverflow.com/questions/15595289/linq-to-entities-join-vs-groupjoin/15599143#15599143), which is much easier in query syntax. Or, preferably, use a navigation property `spl.GeographyPostCodesGeolocations` which will translate as outer join automatically. – Gert Arnold Dec 13 '22 at 13:07
  • What do you mean 'if the join fails'? – nilsK Dec 13 '22 at 13:09

0 Answers0