IsWithinDistance could not be translated

Question

I'm trying to return a list of items that are within say 50 Miles of a given location.

My table (simplified) is as follows:

Id
Longitude
Latitude
Status
Active

I've got an initial query:

var query = db.MyTable.Where(o=> o.Status == "New" && o.Active == true);

query = query.Where(o => new Point(o.Longitude, o.Latitude)
                  .IsWithinDistance(new Point(_currentLongitude, _currentLatitude), 50));

var result = query.ToList()

However - it doesn't seem to work and am getting an error as below - any ideas how to solve this? or if there is a better way to get the nearest items?

.Where(p => new Point(p.Longitude, p.Latitude) .IsWithinDistance(geom: __p_3,
distance: ___maxDistance_4))' could not be translated.

Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Startup.cs:

services.AddDbContext<AppDbContext>(options =>
{
                
 options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"),
   x => x.UseNetTopologySuite());
});

You need to do exactly as the error message suggests: *"Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information"* — Robert Harvey, Dec 09 '20 at 21:21
@RobertHarvey - That's a built-in function in `Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite`. See [EF Core Spatial Data](https://learn.microsoft.com/en-us/ef/core/modeling/spatial) and [Spatial Data in the SQL Server EF Core Provider](https://learn.microsoft.com/en-us/ef/core/providers/sql-server/spatial) — Matt Johnson-Pint, Dec 09 '20 at 21:22
@Steve - Did you add `.UseNetTopologySuite` [as shown here](https://learn.microsoft.com/en-us/ef/core/modeling/spatial#nettopologysuite)? (Also, I am assuming SQL Server, please confirm or correct.) — Matt Johnson-Pint, Dec 09 '20 at 21:23
As far as I can tell, creating a new `Point` from a latitude/longitude is not supported at this point. You might need to store your data as a `Point` type on your model. — ESG, Dec 09 '20 at 21:25
@RobertHarvey hi - yes tried that `var xx = query = (IQueryable)query.Where(o => new Point(o.Longitude, o.Latitude) .IsWithinDistance(new Point(_profile.Longitude, _profile.Latitude), _maxDistance)).ToList();` but still same error — Steve, Dec 09 '20 at 21:35
@MattJohnson-Pint Yes - MS SQL 2019 Express, and yes, already added to startup.cs (have edited answer to show what i had included) — Steve, Dec 09 '20 at 21:37
would you able to test first part of where covert to list then run the second filter `db.MyTable.Where(o=> o.Status == "New" && o.Active == true).ToList();.` — coder_b, Dec 09 '20 at 22:30
@coder_b - Doing so would defeat the purpose of spatial queries/indexes on the SQL side, and potentially move a lot of data unnecessarily to the client. Of course it all depends on how much data we're talking about, but I'd recommend against that approach. — Matt Johnson-Pint, Dec 10 '20 at 17:28
I completely agree, I was checking IsWithinDistance is working as expected with disconnected dataset. wonder whether any issues related configurations etc to rule out.. — coder_b, Dec 10 '20 at 18:22

Matt Johnson-Pint · Accepted Answer · 2020-12-10T00:37:59.660

6

To make this feature work, you will need to have those coordinates stored as a Point in a SQL geography field. You can easily add this to your existing model as a computed column on a new property.

// on your entity
public Point Coordinates { get; }

// in your db context's OnModelCreating
  modelBuilder.Entity<YourEntity>()
    .Property(x => x.Coordinates)
    .IsRequired()
    .HasComputedColumnSql("geography::Point(Latitude, Longitude, 4326)");

Note, SRID 4326 is the commonly used lat/lon coordinate system supported by SQL Server. More on that here.

Build your model and deploy to your database.

Now that you have a spatial field and property, you can query like this:

var point = new Point(_currentLongitude, _currentLatitude) { SRID = 4326 };
var distanceInMeters = 50 * 1609.344;  // 50 miles to meters conversion
var results = db.YourEntity
                .Where(x => x.Coordinates.IsWithinDistance(point, distanceInMeters))
                .ToList();

SRID 4326 uses meters for distance, so be sure to convert as shown above if you are using miles.

Also, if you have a lot of data you will also want to add a spatial index on that column, but EF Core doesn't support that directly yet so you'll have to do that directly in SQL.

edited Dec 10 '20 at 00:37

answered Dec 09 '20 at 23:19

Matt Johnson-Pint

230,703
74
448
575

Note, the computed column approach is optional. You could always just have a `Point` property by itself, but then you wouldn't have separate lat/lon properties. – Matt Johnson-Pint Dec 09 '20 at 23:45
Hi thanks for the quick reply i'll have a go at this. Is there any benefit of having / not having a computed column for the Point? – Steve Dec 10 '20 at 14:40
The `Point` (sql `geography`) field is the necessary data to make spatial queries work. You could just populate a `Point` and leave the separate lat/lon fields off. Having both is mostly just a convenience. – Matt Johnson-Pint Dec 10 '20 at 17:27
@MattJohnson-Pint would you please have a look at this one https://stackoverflow.com/questions/70847958/iswithindistance-method-doesnt-work-with-geojson-file-for-library-topologysuite – Fadi Jan 25 '22 at 11:37

IsWithinDistance could not be translated

1 Answers1

Linked