SqlServer Spatial: Cannot call methods on varbinary

[oceanwebsystems]

Issue

SqlException when testing if a polygon contains a point.

I have a table in the database called Address with a Location column (of GEOGRAPHY data type). The goal is (ultimately) for a user to draw a boundary polygon on a map and the database query will return all records where the Location is within the polygon. However I'm seeing the following exception when executing the code.

Exception message:
   System.Data.SqlClient.SqlException: 'Cannot call methods on varbinary.'
Stack trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EfCoreSpatialData.Program.Test01() in C:\Dev\Demos\EfCoreSpatialData\EfCoreSpatialData\Program.cs:line 52
   at EfCoreSpatialData.Program.Main(String[] args) in C:\Dev\Demos\EfCoreSpatialData\EfCoreSpatialData\Program.cs:line 17

Steps to reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using GeoAPI.Geometries;
using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;

namespace EfCoreSpatialData
{
    class Program
    {
        private static SpatialDataContext dataContext;

        static void Main(string[] args)
        {
            SetDataContext();
            Test01();
            Console.WriteLine("Press ENTER to continue.");
            Console.ReadLine();
        }

        private static void Test01()
        {

            Point addressLocation = new Point(-4, 55)
            {
                SRID = 4326
            };

            List<Coordinate> searchCoordinates = new List<Coordinate>
            {
                new Coordinate(-5, 56),
                new Coordinate(-3, 56),
                new Coordinate(-3, 54),
                new Coordinate(-5, 54),
                new Coordinate(-5, 56)
            };

            Coordinate[] searchCoordinatesArray = searchCoordinates.ToArray();
            LinearRing searchLinearRing = new LinearRing(searchCoordinatesArray);
            Polygon searchPolygon = new Polygon(searchLinearRing)
            {
                SRID = 4326
            };

            IGeometry searchGeometry = searchPolygon.Normalized().Reverse();
            searchGeometry.SRID = 4326;

            bool locationIsInSearchPolygon = searchGeometry.Contains(addressLocation);
            Console.WriteLine(locationIsInSearchPolygon); // This is true.
            
            List<Address> addresses = dataContext.Addresses.Where(x => searchGeometry.Contains(x.Location)).ToList(); // This throws System.Data.SqlClient.SqlException: 'Cannot call methods on varbinary.'
            foreach (var address in addresses)
            {
                Console.WriteLine(address.Line1);
            }
        }

        private static void SetDataContext()
        {
            var optionsBuilder = new DbContextOptionsBuilder<SpatialDataContext>();
            var connectionString = "Server=.;Database=Spatial;Trusted_Connection=True;";
            optionsBuilder.UseSqlServer(connectionString, options => options.UseNetTopologySuite());
            dataContext = new SpatialDataContext(optionsBuilder.Options);
        }
    }
}

Address.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using NetTopologySuite.Geometries;

namespace EfCoreSpatialData
{
    [Table(name: nameof(Address))]
    public class Address
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public string Line1 { get; set; }

        public string Line2 { get; set; }

        public string Line3 { get; set; }

        public string Line4 { get; set; }

        public string Postcode { get; set; }

        public Point Location { get; set; }
    }
}

SpatialDataContext.cs

using Microsoft.EntityFrameworkCore;

namespace EfCoreSpatialData
{
    public class SpatialDataContext : DbContext
    {
        public SpatialDataContext(DbContextOptions<SpatialDataContext> options)
            : base(options)
        { }

        public DbSet<Address> Addresses { get; set; }
    }
}

USE [Spatial]
GO
/****** Object:  Table [dbo].[Address]    Script Date: 03/02/2019 23:29:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Address](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Line1] [nvarchar](50) NULL,
	[Line2] [nvarchar](50) NULL,
	[Line3] [nvarchar](50) NULL,
	[Line4] [nvarchar](50) NULL,
	[Postcode] [nvarchar](50) NULL,
	[Location] [geography] NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Source solution also available at: https://gitlab.com/stuartwhiteford/efcorespatialdata

The SQL generated is as follows:

exec sp_executesql N'SELECT [x].[Id], [x].[Line1], [x].[Line2], [x].[Line3], [x].[Line4], [x].[Location], [x].[Postcode]
FROM [Address] AS [x]
WHERE @__searchGeometry_0.STContains([x].[Location]) = 1',N'@__searchGeometry_0 varbinary(112)',@__searchGeometry_0=0xE61000000104050000000000000000004B4000000000000014C00000000000004B4000000000000008C00000000000004C4000000000000008C00000000000004C4000000000000014C00000000000004B4000000000000014C001000000020000000001000000FFFFFFFF0000000003

but the following DOES work:

exec sp_executesql N'SELECT [x].[Id], [x].[Line1], [x].[Line2], [x].[Line3], [x].[Line4], [x].[Location], [x].[Postcode]
FROM [Address] AS [x]
WHERE @__searchGeometry_0.STContains([x].[Location]) = 1',N'@__searchGeometry_0 geography',@__searchGeometry_0=0xE61000000104050000000000000000004B4000000000000014C00000000000004B4000000000000008C00000000000004C4000000000000008C00000000000004C4000000000000014C00000000000004B4000000000000014C001000000020000000001000000FFFFFFFF0000000003

Further technical details

EF Core version: 2.2.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer 2.2.1
Using: Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite 2.2.1
Operating system: Windows 10 Pro - Version 1803 - OS Build 17134.523
IDE: (e.g. Visual Studio 2017 15.9.6)

[mohamedmahmoudalemam]

List<Address> addresses = dataContext.Addresses.Where(x => searchGeometry.Contains(x.Location)).ToList();
//update this line to ->
var addresses = dataContext.Addresses.ToList().Where(x => searchGeometry.Contains(x.Location));
//evaluate client side method after get all addresses you want to check against.

[bricelam]

This is a limitation caused by using System.Data.SqlClient without Microsoft.SqlServer.Types--we can't specify the type of the SqlParameter.

Workaround

Rewrite the expression to begin with a column (or a new geometry) instead of a variable:

dataContext.Addresses.Where(x => x.Location.Within(searchGeometry))

[ajcvickers]

Notes from triage:

• Consider filing an issue on SqlClient for this. (That is, allowing the parameter to indicate that a spatial type is coming in the payload, even if it was but there as raw bytes instead of through the UDT itself.)
• This could potentially be fixed with server-side conversions.
• Otherwise blocked on SqlClient supporting spatial types.

[AblEdge]

This is a limitation caused by using System.Data.SqlClient without Microsoft.SqlServer.Types--we can't specify the type of the SqlParameter.

Workaround

Rewrite the expression to begin with a column (or a new geometry) instead of a variable:

dataContext.Addresses.Where(x => x.Location.Within(searchGeometry))

I had the same issue, its ok

[ajcvickers]

Next steps: @bricelam file an issue on corefx.

[bricelam]

Filed https://github.com/dotnet/corefx/issues/35470

[snakenstein]

Rewrite the expression to begin with a column (or a new geometry) instead of a variable

@bricelam could you please provide an example of rewriting the expression to use new geometry?

We are experiencing a performance issue with STDistance() on SQL Server 2016 when searching a big table for records with geography column that are within a given distance from a given point.

[BigTable].[GeographyColumn].STDistance(@originGeoPoint) <= @distance)

works much slower then

@originGeoPoint.STDistance([BigTable].[GeographyColumn]) <= @distance)

So, rewriting an expression to begin with a column is not an option in this case.

We tried rewriting the expression to start with a CreatePoint() method call on GeometryFactory instance. But it still translates in @__CreatePoint_0.STDistance() where @__CreatePoint_0 is varbinary(22). What leads to "Cannot call methods on varbinary"

[ajcvickers]

@snakenstein When you say, "works much slower than" can you provide some details on:

• How you are executing the queries
• How you are measuring the performance
• What the relative different in performance is

This will help us understand the impact here.

[snakenstein]

@ajcvickers Sorry for the delay. In short, answers to your questions are:

• we run queries in MS SQL Server Management Studio
• we use SQL Server statistics and SQL Server Profiler trace data
• 2-3 times faster when calling STDistance() on variable

We have caught a query that EF Core was issuing to the database in SQL Server Profiler and started investigating the problem in SQL Server Management Studio.
Here is that query (we declared and set values to variables manually).

declare @__location_0 geography,
		@__distance_1 int
select @__location_0 = geography::Point(55.755826, 37.617299900000035, 4326);
select @__distance_1 = 100000

SELECT COUNT(*)
FROM [Resumes] AS [wp]
INNER JOIN [Workers] AS [wp.Worker] ON [wp].[WorkerId] = [wp.Worker].[Id]
INNER JOIN [Customers] AS [wp.Worker.Customer] ON [wp.Worker].[Id] = [wp.Worker.Customer].[Id]
INNER JOIN [Addresses] AS [wp.Worker.Customer.Address] ON [wp.Worker.Customer].[AddressId] = [wp.Worker.Customer.Address].[Id]
WHERE ([wp].[WorkType] = 1) 
AND (((([wp].[IsPublished] = 1) AND ([wp].[ModerationStatus] <> 2)) AND ([wp.Worker.Customer].[IsDeactivated] = 0)) 
AND ([wp.Worker.Customer.Address].[Location].STDistance(@__location_0) <= @__distance_1))

We want to get count of resumes from workers that has address located within 100 km from a given point (plus some conditions on resume and customer).
Table [Addresses] has spatial index on [Location] column.

In our test database this query executes 7-8 seconds.

Table 'Resumes'. Scan count 11, logical reads 828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'extended_index_597577167_384000'. Scan count 591, logical reads 3072, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 0, logical reads 729204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Addresses'. Scan count 0, logical reads 729204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 16, logical reads 384, physical reads 16, read-ahead reads 368, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 7421 ms,  elapsed time = 1901 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

When we changed the query to call STDistance on @__location_0 variable and pass [wp.Worker.Customer.Address].[Location] column as an argument, we got 2-3 seconds execution time on the same database.

Table 'Workfile'. Scan count 22, logical reads 904, physical reads 70, read-ahead reads 842, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Addresses'. Scan count 0, logical reads 601036, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 0, logical reads 729144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'extended_index_597577167_384000'. Scan count 591, logical reads 3072, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Resumes'. Scan count 2, logical reads 783, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 2281 ms,  elapsed time = 2378 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

The difference in execution plans of this queries is in this part:
unmodified query: http://prntscr.com/nbdsnt
call STDistance() on variable: http://prntscr.com/nbdsv5

We are running MS SQL Server 2016 SP2 (13.0.5081.1)

[bricelam]

This should be as trivial as updating this method to leverage the feature added to SqlClient.