efcore: NetTopologySuite SqlServer provider incorrectly translates Within query

I have this query:

var points = new[]
{
	new Coordinate(neLng.Value, neLat.Value), // TR
	new Coordinate(neLng.Value, swLat.Value), // BR
	new Coordinate(swLng.Value, swLat.Value), // BL
	new Coordinate(swLng.Value, neLat.Value), // TL
	new Coordinate(neLng.Value, neLat.Value), // TR
};

var fact = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
var poly = fact.CreatePolygon(points).Normalized().Reverse();

// This line is only added to demonstrate that local evaluation works correctly
var list = query.ToList().Where(x => x.Location != null && x.Location.Within(poly)).ToList();

// This does not filter to the correct results; it excludes values that should be included, and includes values that should be excluded
query = query.Where(x => x.Location.Within(poly));

The server-evaluated query is incorrect - it omits points within the polygon poly, and includes ones that are outside it. The locally-evaluated version (“bug demo”) does return the expected results. They should be returning the same results since they are doing the same thing. query is IQueryable.

The values received for the bounds will be like this:

neLat	60.11131083608817
neLng	-74.22879847513246
swLat	47.2231665957533
swLng	-157.85672816263252

Further technical details

EF Core version: 2.2.3 Database Provider: Microsoft.EntityFrameworkCore.SqlServer

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 36 (9 by maintainers)

Most upvoted comments

@bricelam That screenshot was just a demonstration. This is the one that was actually in use:

And in the code, the points are defined in counter-clockwise order:

var points = new[]
{
	new Coordinate(neLng.Value, neLat.Value), // Top Right
	new Coordinate(swLng.Value, neLat.Value), // Top Left
	new Coordinate(swLng.Value, swLat.Value), // Bottom Left
	new Coordinate(neLng.Value, swLat.Value), // Bottom Right
	new Coordinate(neLng.Value, neLat.Value), // Top Right
};

var fact = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
var poly = fact.CreatePolygon(points);

var within = query.Where(x => x.Location != null)
	.ToList() // Force local evaluation
	.Where(x => x.Location.Within(poly))
	.ToList();

return Ok(PagedData<Shop>.FromList(within));

//query = query
//    .Where(x => x.Location.Within(poly));

DanielStout5 on Oct 15, 2019

I think this might be part of the issue:

Turns out SSMS has a Spatial Results visualizer:

declare @poly varbinary(112) = 0xE610000001040500000079DA50E81999434094D172A355B75AC079DA50E8199943404046CB8D963C37C0EAC7100794B84B404046CB8D963C37C0EAC7100794B84B4094D172A355B75AC079DA50E81999434094D172A355B75AC001000000020000000001000000FFFFFFFF0000000003;
declare @pt varbinary(112) = 0xE6100000010C3CA06CCA15524640A089B0E1E9C94FC0;

select geography::Deserialize(@poly).STAsText();
select geography::Deserialize(@pt).STAsText()
select geography::Deserialize(@pt).STIntersects(@poly)

select geography::Deserialize(@poly) union all select geography::Deserialize(@pt)

That little black dot is the @pt

So the point would be within the polygon if it had straight lines, but they connected by curves. Perhaps Reverse would just flip the polygon on the X plane - so it might work for the bottom points but wouldn’t work for the top ones…

Do you know if there is some alternative way to give that polygon to EF Core that would change how it interprets the coordinates?

DanielStout5 on May 14, 2019

It seems that the format I’m getting from the SQL Server Profiler (which starts with 0xE61…) isn’t actually WKB, and I’m not sure how to convert it into a more readable format.

Correct, it’s the SQL Server serialization format. You can turn it into WKT like this:

SELECT geography::Deserialize(0xE61...).STAsText()

bricelam on May 14, 2019

Hello, I am one of colleague whose the reporter #15668:

Some experience of mine would help :

Type declare of the Location column in the SQLContext directs to be a "geometry"or “geography”, if there is none ,the default will be a “geography”. it looks like : entity.Property(e => e.Location).HasColumnType("geometry");
Detect counter clockwise then we can decide to reverse the polygon or not.
Or you could try to use Location.intersects(poly)?
Post some translated SQL or error message that are from [ASP.NET Core Web Server] window at bottom of Visual Studio when running the web app, you might get more help.

JediLeeSkywalker on May 14, 2019