ST Geometry with Entity Framework

Today I learned how to use SQL Server Spatial types with Microsoft Entity Framework. The Microsoft reference document Spatial Data — EF Core gave me all of the information I needed, but I would like to walk you through my specific use case.

I am rewriting a dot NET application to use Entity Framework Core rather than straight SQL queries. Today I encountered a query that looks like this:

SELECT SUM(SHAPE.STLength()) FROM dbo.WMAIN WHERE MAINTAINED = 'CITY' AND (OPSTATUS = 'SERVICE' OR OPSTATUS = 'PROB') AND (DIAMETER >= 16)

SHAPE.STLength() is not convertible to vanilla Entity Framework. Here’s the steps I took to make it work.

Step One

I installed the NuGet package NetTopologySuite
https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite

Step Two

In the Startup.cs file, where I configure my DbContext classes, I added the bold part:

services.AddDbContext<GISContext>(options => options.UseSqlServer(Configuration.GetConnectionString("GISContext"), x => x.UseNetTopologySuite()); 

This option allows for spatial data types.

Step Three

In my WMain.cs model that mirrors the database table, I added the SHAPE field. It had not been added during the initial scaffolding, because its type was not recognized, since I had not yet installed the topology package. To accomplish this, I added the lines:

using NetTopologySuite.Geometries;
public LineString Shape { get; set; }

This could have also been public Geometry Shape { get; set; }
But I decided to make it more specific since I know the geometry type.

Step Four

With all of this in place, I could now write an Entity Framework command to calculate the sum of the feature lengths:

var selectedRecords = from rows in _context.WMain where rows.Maintained.Equals("CITY") && (rows.Opstatus.Equals("SERVICE") || rows.Opstatus.Equals("PROB")) && rows.Diameter >= 16 select rows;
var lengthFeet = await selectedRecords.SumAsync(x => x.Shape.Length);

This is a useful technique to know for any GIS dot NET programmer who’s accessing a geodatabase.