0

I have trying to get nearby places using DBGeography pointtotext using LINQ using following query. My purpose is to offload distance calculation from .net core and put it on server. Query is for mobile application

List<int> list = await (from instance in _applicationDbContext.ApplicationInstances
                                join sub in (from i in _applicationDbContext.ApplicationInstances
                                             join ad in _applicationDbContext.Addresses
                                             on i.AddressId equals ad.AddressId
                                             select new returnvals
                                             {
                                                 ApplicationInstanceId = i.ApplicationInstanceId,
                                                 distance = DbGeography.PointFromText($"POINT({ad.Longitude} {ad.Latitude})", 4326)
                                                             .Distance(
                                                              DbGeography.PointFromText($"POINT({locationVM.Longitude} {locationVM.Latitude})", 4326))
                                             }
                    ) on instance.ApplicationInstanceId equals sub.ApplicationInstanceId
                                where sub.distance <= locationVM.Distance * 1000
                                select (instance.ApplicationInstanceId)).ToListAsync();

But I have been getting error

The LINQ expression 'DbSet()\r\n .Where(a => !(a.IsDeleted))\r\n .Join(\r\n inner: DbSet()\r\n .Where(a0 => !(a0.IsDeleted))\r\n .Join(\r\n inner: DbSet()\r\n .Where(a1 => !(a1.IsDeleted)), \r\n outerKeySelector: a0 => a0.AddressId, \r\n innerKeySelector: a1 => a1.AddressId, \r\n resultSelector: (a0, a1) => new TransparentIdentifier<ApplicationInstance, AddressMaster>(\r\n Outer = a0, \r\n Inner = a1\r\n )), \r\n outerKeySelector: a => a.ApplicationInstanceId, \r\n innerKeySelector: ti => ti.Outer.ApplicationInstanceId, \r\n resultSelector: (a, ti) => new TransparentIdentifier<ApplicationInstance, TransparentIdentifier<ApplicationInstance, AddressMaster>>(\r\n Outer = a, \r\n Inner = ti\r\n ))\r\n .Where(ti0 => DbGeography.PointFromText(\r\n pointWellKnownText: string.Format(\r\n format: "POINT({0} {1})", \r\n arg0: (object)ti0.Inner.Inner.Longitude, \r\n arg1: (object)ti0.Inner.Inner.Latitude), \r\n coordinateSystemId: 4326).Distance(__PointFromText_0) <= __p_1)' could not be translated

The error is always because when I try to put some condition on sub.distance

I have tried separating the queries as well, First query for Sub query of above code then another query to compare the distance.

I only want the place (ApplicationInstance) id and it's distance.

Ajay Kumar
  • 11
  • 3
  • https://stackoverflow.com/a/65226347/6527049 – Vivek Nuna Nov 28 '22 at 07:25
  • This answer uses NetTopologySuite. I have simple column (decimal) in Adress table. Is there any work around to do this without using NetTopologySuite. – Ajay Kumar Nov 28 '22 at 07:35
  • Not a real answer, but you could use separate conditions on latitude and longitude ranges to at least limit locations returned from the server to those that could possibly be within your desired distance. Then do the final distance limit on the client. – pjs Nov 28 '22 at 13:41

0 Answers0