Is there a way to use SqlSpatialFunctions without Linq to Entities?

Question

From System.Data.Entity.SqlServer.SqlSpatialFunctions class definition:

Contains function stubs that expose SqlServer methods in Linq to Entities.

I know that Linq to Entities is mentioned specifically, however I was wondering if some functionality can be replicated in my back end code.

I've got an account Model, with lazy loaded collection of Polygons. Each Polygon contains a DbGeography type property called Location.

I'm trying to get all Polygons that interesct certain point (which also has lazy loaded property Address, which has DbGeography type property called Location).

I can do it like this:

var matchedPolygons =
    account.Polygons.Where(
       x =>
          point.Address.Location.Intersects(x.Polygon.Location)).ToList();

which works fine.

In order to try to improve the performance, I thought it would be a good idea to slightly reduce the Polygon data.

var matchedPolygons =
    account.Polygons.Where(
       x =>
          point.Address.Location.Intersects(SqlSpatialFunctions.Reduce(x.Polygon.Location, 100))).ToList();

This however throws the following System.NotSupportedException exception:

This function can only be invoked from LINQ to Entities.

I know I can retrieve the polygons directly from my repository layer using the Reduce method above, but since I'm using lazy loading and have Polygons collection available to me already, I thought there might be a way of using SqlSpatialFunctions at this stage.

score 0 · Answer 1 · answered Aug 30 '17 at 01:17

The only thing I've ever been able to come up with a gross hack. It works, but I make no claim as to elegance.

Toss in these includes (make the references as well):

using System.Data.Entity.Spatial;
using System.Data.SqlTypes;

Convert the DbGeography to a SqlGeography:

    Microsoft.SqlServer.Types.SqlGeography sqlGeography = Microsoft.SqlServer.Types.SqlGeography.STGeomFromWKB(new SqlBytes(geog.AsBinary()), mydbgeog.CoordinateSystemId);

Maybe it's okay if you keep it as SqlGeography, but if not convert it back.

sqlGeography = sqlGeography.Reduce(50);

mydbgeog = DbGeography.FromBinary(sqlGeography.STAsBinary().Value);

It's the fastest way I know of to toss back and forth between DbGeography and SqlGeography. Again, it's gross and requires an additional library to be in place but honestly there's a lot of stuff in SqlGeography that for a big GIS application will probably need.

Is there a way to use SqlSpatialFunctions without Linq to Entities?

1 Answers1