Geography SQL type support in .NET Core 6 / Entity Framework Core

Question

I have a .NET Core 6 application and SQL Server database which has a table with a column defined as type Geography.

My goal is to use a Linq query to retrieve a list of rows where the Geography Distance < 4000 meters from a point.

The following T-SQL works fine:

-- SPATIAL WITHIN DISTANCE
DECLARE @Origin GEOGRAPHY,
        -- distance defined in meters
        @Distance INTEGER = 40000;        

-- center point
SET @Origin = GEOGRAPHY::STGeomFromText('POINT(-93.5663 44.9033)', 4326);

-- return all rows from events in 40km radius
SELECT * FROM dbo.GeographyAreas WHERE @Origin.STDistance(Geography) <= @Distance;

I would prefer to implement this code in my application data layer and use LINQ syntax rather than a stored procedure using the above code. But there seems to be a mismatch between SQL Server Geography column type and the spatial libraries Microsoft offers:

Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
NetTopologySuite.Core
Microsoft.Spatial
Microsoft.SqlServer.Types

NetTopologySuite supports the Geometry type, but not Geography from what I see.

Microsoft.Spatial Geography type will not map to the SQL Server Geography type.

Microsoft.SqlServer.Types appears to be for .NET (classic) not .NET Core.

I am looking for something more like this:

public class GeographyAreas()
{
   public <SomeGeographyType> Geography { get; set; }
}

var origin = <SomeGeographyType>(-93.123, 44.456);
var dist = 40000;

List<GeographyAreas> results = _context.GeographyAreas.Where(x => x.Geography.Distance(origin) < dist).ToList();

where <SomeGeographyType> is a placeholder for any of these:

Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
Microsoft.Spatial

When I use this approach I get an error:

The Geography property of GeographicAreas.Geography could not be mapped because the database provider does not support this type

Is it possible to do this in Entity Framework Core or does it make sense to implement a stored procedure with the T-SQL code above?

Answer 1

I solved this myself. As it turns out there is a video where the fact that the Geography aspect of NetTopologySuite is described. It is intentionally left out (but the requirement solved in an admittedly roundabout way).

According to this video here when creating the model in Entity Framework, the data type to use is Geometry (even for a Geography SQL Server data type). The decision by the NetTopologySuite team was made to not add Geography-specific features because, in fact, the code used to work with Geography and Geometry is identical. This is not to say that a globe-based Geography value is the same as a Cartesian-based Geometry one; just that the code to operate on them is the same.

I was in fact able to get this to work using LINQ query. If interested I will post the full solution here.