0

I have this working code:

IEnumerable<Message> dbCities = db.Cities;

dbCities = dbCities.Where(x => GetDistanceBetweenLocations(longitude, latitude, x.Longitude, x.Latitude) <= radius);

public int GetDistanceBetweenLocations(double lon1, double lat1, double lon2, double lat2)
{
        var sCoord = new GeoCoordinate(lon1, lat1);
        var eCoord = new GeoCoordinate(lon2, lat2);

        return Convert.ToInt32(Math.Round(sCoord.GetDistanceTo(eCoord)));
}

But it is kind of slow. I'm trying to move the logic in the DB for better performance.

I started with few things like:

dbCities = db.Cities.Where(x => new GeoCoordinate(longitude, latitude).GetDistanceTo(new GeoCoordinate(x.Longitude, x.Latitude)) <= radius);

And got: LINQ to Entities does not recognize the method 'Double GetDistanceTo(System.Device.Location.GeoCoordinate)' method, and this method cannot be translated into a store expression.

I know about the Haversine formula but could'n make it work with linq.

The project uses a SQL Server DB and code-first approach.

Vlad C
  • 447
  • 2
  • 6
  • 17

1 Answers1

1

The error message speaks for itself: the LINQ provider of EF has no idea how to map a function named GetDistanceTo to its SQL counterpart.

If you want the task to be done by the underlying DBMS you need to rewrite your query to use only canonical functions. Alternatively, you can define your logic as an UDF in your DB (if your DB engine supports UDFs) and introduce it to EF.

However, if you want to handle this in the application, you can "force" your LINQ to Entites query into a LINQ to Objects one:

dbCities = db.Cities.AsEnumerable().Where(x => new GeoCoordinate(longitude, latitude).GetDistanceTo(new GeoCoordinate(x.Longitude, x.Latitude)) <= radius);

However, be careful with this: using this approach every city record will be fetched from the DB to be examined by your GetDistanceTo logic.

Update

As of version 2012, SQL Server provides a built-in geography API. It can calculate distances, as well.

You can access this API in EF through the DbGeography class. Have a look at this discussion.

If you use EF Core, you need a workaround as DbGeography is not ported yet, it's expected in version 2.2.0. (However, the UDF mapping approach mentioned earlier should work.)

Adam Simon
  • 2,762
  • 16
  • 22
  • I know this... But as I said, I need the logic to be executed in the DB (IQueryable). This is not different from my initial code, it doesn't solve the performance issue. – Vlad C Aug 18 '18 at 09:59
  • 1
    "I need the logic to be executed in the DB (IQueryable)". My answer covers this: you need to implement your logic using canonical functions or to use UDFs. No other ways if you want to have the DB do the job. – Adam Simon Aug 18 '18 at 10:59
  • Canonical functions and UDFs for finding geographical distance between coordinates figured out to be hard to implement and manage with Entity Framework, but your update helped me to accomplish this. Thank you. – Vlad C Feb 04 '19 at 09:37