Getting Entity Framework to work with SQL Server geospatial indexes

Question

We've got a fairly simple table that we're trying to query with Entity Framework, that looks something like this:

CREATE TABLE [dbo].[EFBlkFireRisks](
    [EFBlkFireRiskId] [int] IDENTITY(1,1) NOT NULL,
    [EFStateId] [int] NOT NULL,
    [Lat] [decimal](18, 8) NOT NULL,
    [Lon] [decimal](18, 8) NOT NULL,
    [AdjustedPremiumMultiplier] [decimal](18, 4) NOT NULL DEFAULT ((0)),
    [GeoLocation] [geography] NULL,
 CONSTRAINT [PK_dbo.EFBlkFireRisks] PRIMARY KEY CLUSTERED 
(
    [EFBlkFireRiskId] ASC
)

It has a geospatial index on the GeoLocation column, like so:

CREATE SPATIAL INDEX [IX_EFBlkFireRisks_Spatial] ON [dbo].[EFBlkFireRisks]
(
    [GeoLocation]
)USING  GEOGRAPHY_AUTO_GRID

We're trying to find the entry in that table that's the closest to a given lat/lon point.

The C# query looks like this:

var geoPoint = SqlSchemaFunctions.GetDbGeography(lat, lon);
var query = ctx.BulkFireRisks
    .Where(x => x.EFStateId == stateId && x.GeoLocation.Distance(geoPoint) != null)
    .OrderBy(x => x.GeoLocation.Distance(geoPoint));
var bfr = query.First();

The SQL that query generates looks basically like this:

declare @p3 sys.geography set @p3=convert(sys.geography,0xE6100000010C8FC2F5285CFF434052B81E85EB515AC0)
declare @p4 sys.geography set @p4=convert(sys.geography,0xE6100000010C8FC2F5285CFF434052B81E85EB515AC0)
exec sp_executesql N'SELECT TOP (1) 
    [Project1].[EFBlkFireRiskId] AS [EFBlkFireRiskId], 
    [Project1].[EFStateId] AS [EFStateId], 
    [Project1].[GeoId] AS [GeoId], 
    [Project1].[Lat] AS [Lat], 
    [Project1].[Lon] AS [Lon], 
    [Project1].[GeoLocation] AS [GeoLocation], 
    [Project1].[AdjustedPremiumMultiplier] AS [AdjustedPremiumMultiplier],
    [Project1].C1 as Distance
    FROM ( SELECT 
        [Extent1].[GeoLocation].STDistance(@p__linq__1) AS [C1], 
        [Extent1].[EFBlkFireRiskId] AS [EFBlkFireRiskId], 
        [Extent1].[EFStateId] AS [EFStateId], 
        [Extent1].[GeoId] AS [GeoId], 
        [Extent1].[Lat] AS [Lat], 
        [Extent1].[Lon] AS [Lon], 
        [Extent1].[GeoLocation] AS [GeoLocation], 
        [Extent1].[AdjustedPremiumMultiplier] AS [AdjustedPremiumMultiplier]
        FROM [dbo].[EFBlkFireRisks] AS [Extent1]
        WHERE (11 = [Extent1].[EFStateId]) AND ([Extent1].[GeoLocation].STDistance(@p__linq__0) IS NOT NULL)
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC',N'@p__linq__0 [geography],@p__linq__1 [geography]',@p__linq__0=@p3,@p__linq__1=@p4

And that query does not use the index - placing it in a loop and running it 10 times takes roughly 30 seconds (whether I execute it manually via SQL, or run it via EF from C#).

However, it's possible to remove the sp_executesql bit, and run the query directly, like so:

declare @p3 sys.geography set @p3=convert(sys.geography,0xE6100000010C8FC2F5285CFF434052B81E85EB515AC0)
SELECT TOP (1) 
    [Project1].[EFBlkFireRiskId] AS [EFBlkFireRiskId], 
    [Project1].[EFStateId] AS [EFStateId], 
    [Project1].[GeoId] AS [GeoId], 
    [Project1].[Lat] AS [Lat], 
    [Project1].[Lon] AS [Lon], 
    [Project1].[GeoLocation] AS [GeoLocation], 
    [Project1].[AdjustedPremiumMultiplier] AS [AdjustedPremiumMultiplier],
    [Project1].C1 as Distance
    FROM ( SELECT 
        [Extent1].[GeoLocation].STDistance(@p3) AS [C1], 
        [Extent1].[EFBlkFireRiskId] AS [EFBlkFireRiskId], 
        [Extent1].[EFStateId] AS [EFStateId], 
        [Extent1].[GeoId] AS [GeoId], 
        [Extent1].[Lat] AS [Lat], 
        [Extent1].[Lon] AS [Lon], 
        [Extent1].[GeoLocation] AS [GeoLocation], 
        [Extent1].[AdjustedPremiumMultiplier] AS [AdjustedPremiumMultiplier]
        FROM [dbo].[EFBlkFireRisks] AS [Extent1]
        WHERE (11 = [Extent1].[EFStateId]) AND ([Extent1].[GeoLocation].STDistance(@p3) IS NOT NULL)
    )  AS [Project1]
    ORDER BY [Project1].[C1] asc

And that query takes not 30 seconds to run, but 30 milliseconds.

I'm scratching my head. Any idea why the two queries have such radically different execution patterns? Any idea how to get EF to work with this particular geospatial index? What am I doing wrong?

EDIT 6/18 - If I try to add an index hint to the slower sp_executesql query, I get this error message:

The query processor could not produce a query plan for a query with a spatial index hint. Reason: Spatial indexes do not support the comparator supplied in the predicate.

The same hint works - or at least, doesn't error out - on the other query.

The query plan for the slow query looks like this:

In other words, it's not picking up the geospatial index. The query plan for the fast version is picking up the index, and looks like you'd expect:

This is running on SQL Server 2014:

(Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 (Build 9600: )

Answer 1

OK, I figured it out.

Turns out that the two queries weren't precisely identical: the sp_executesql version had two parameters, while the fast one only had one: and that was the key. This query (with just one parameter) executes like you'd expect:

declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010C8FC2F5285CFF434052B81E85EB515AC0)
exec sp_executesql N'SELECT TOP (1) 
    [Project1].[EFBlkFireRiskId] AS [EFBlkFireRiskId], 
    [Project1].[EFStateId] AS [EFStateId], 
    [Project1].[GeoId] AS [GeoId], 
    [Project1].[Lat] AS [Lat], 
    [Project1].[Lon] AS [Lon], 
    [Project1].[GeoLocation] AS [GeoLocation], 
    [Project1].[AdjustedPremiumMultiplier] AS [AdjustedPremiumMultiplier]
    FROM ( SELECT 
        [Extent1].[EFBlkFireRiskId] AS [EFBlkFireRiskId], 
        [Extent1].[EFStateId] AS [EFStateId], 
        [Extent1].[GeoId] AS [GeoId], 
        [Extent1].[Lat] AS [Lat], 
        [Extent1].[Lon] AS [Lon], 
        [Extent1].[GeoLocation] AS [GeoLocation], 
        [Extent1].[AdjustedPremiumMultiplier] AS [AdjustedPremiumMultiplier], 
        [Extent1].[GeoLocation].STDistance(@p__linq__0) AS [C1]
        FROM [dbo].[EFBlkFireRisks] AS [Extent1]
        WHERE (11 = [Extent1].[EFStateId]) AND ([Extent1].[GeoLocation].STDistance(@p__linq__0) IS NOT NULL)
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC',N'@p__linq__0 [geography]',@p__linq__0=@p3

And the way to get that query in LINQ is like this:

var geoPoint = DbGeography.PointFromText(string.Format("POINT({0} {1})", lon, lat), 4326);
var q2 = (from b in ctx.BulkFireRisks
          let distance = b.GeoLocation.Distance(geoPoint)
          where b.EFStateId == stateId && distance != null
          orderby distance
          select b);
var bfr = q2.First();

The key is to define any sys.geography variable just once.