Efcore Spatial Query: A number is expected at position 9 of the input. The input has @p0. -

.net .net-core entity-framework-core spatial wkt


I'm trying to get around the fact that Entity Framework Core doesn't have support for Spatial Types by using the DBSet.FromSQL Method, and hand rolling a migration to add a geography column on SQL Server.

Here's my DataContext

public interface IDataContext
    DbSet<PointOfInterest> PointsOfInterest { get; set; }
    int SaveChanges();
    Task<int> SaveChangesAsync(CancellationToken cancellationToken);

public class DataContext : DbContext, IDataContext
    public DataContext(DbContextOptions options) : base(options)


    public DbSet<PointOfInterest> PointsOfInterest { get; set; }


PointOfInterest Model

using System;

namespace EfSpatialSample.Models
    public class PointOfInterest
        public Guid Id { get; set; }
        public double Latitude { get; set; }
        public double Longitude { get; set; }
        public DateTime DateAdded { get; set; }

Migration to add Geography Type

    using Microsoft.EntityFrameworkCore.Migrations;

namespace EfSpatialSample.Migrations
    public partial class InitialModel : Migration
        protected override void Up(MigrationBuilder migrationBuilder)

            migrationBuilder.Sql($"CREATE TABLE [dbo].[PointsOfInterest]" +
"(" +
" [Id] [uniqueidentifier]  NOT NULL DEFAULT NEWSEQUENTIALID(), " +
"[DateAdded] [datetime2](7)  NOT NULL," +
"[Latitude] [float]  NOT NULL,  " +
"[Longitude] [float]  NOT NULL, " +
"[Location] [geography] NOT NULL " +
") " +
"ALTER TABLE [dbo].[PointsOfInterest] ADD CONSTRAINT PK_PointsOfInterest PRIMARY KEY  ([Id])"
+ "CREATE SPATIAL INDEX SIndx_PointsOfInterest_geography_Location ON PointsOfInterest(Location); "


        protected override void Down(MigrationBuilder migrationBuilder)
                name: "PointsOfInterest");

And here's the Query

using System.Collections.Generic;
using System.Globalization;
using System.Threading.Tasks;
using EfSpatialSample.Models;
using Microsoft.EntityFrameworkCore;

namespace EfSpatialSample.Queries
    public class GetPointsOfInterest
        private IDataContext context;

        public GetPointsOfInterest(IDataContext context)
            this.context = context;

        public async Task<List<PointOfInterest>> Execute(double latitude, double longitude, int radius)
            return await this.context.PointsOfInterest.FromSql("SELECT Id, DateAdded, Latitude, Longitude " +
                     "FROM dbo.PointsOfInterest WHERE GEOGRAPHY::STGeomFromText('POINT({0} {1})', 4326).STDistance(Location) <= {2};"
                     , longitude.ToString(CultureInfo.InvariantCulture)
                     , latitude.ToString(CultureInfo.InvariantCulture)
                     , radius.ToString(CultureInfo.InvariantCulture)).ToListAsync();

Called from Controller

    public async Task<IEnumerable<PointOfInterest>> Get()
        var query = new GetPointsOfInterest(this.context);

        return await query.Execute(0,0, 1000000);

Stack trace of error is

    fail: Microsoft.AspNetCore.Server.Kestrel[13]
      Connection id "0HL0JK1F6G9EP": An unhandled exception was thrown by the application.
System.Data.SqlClient.SqlException: A .NET Framework error occurred during execution of user-defined routine or aggregate "geography
System.FormatException: 24141: A number is expected at position 9 of the input. The input has @p0.
   at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeDouble()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boole
an asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpl
eResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.<>c__DisplayClass184_0.<ReadAsync>b__1(Task t)
   at System.Data.SqlClient.SqlDataReader.InvokeRetryable[T](Func`2 moreFunc, TaskCompletionSource`1 source, IDisposable objectToDis

If i hard code the query parameters into the query string, the query completes successfully, so it appears to be a problem with with the query params object.

12/7/2016 9:16:14 AM

Accepted Answer

The only way i could get this to work is by not using WKT methods

i.e.: geography::Point() instead of GEOGRAPHY::STGeomFromText()

public async Task<List<PointOfInterest>> Execute(double latitude, double longitude, int radius)
        return await this.context.PointsOfInterest.FromSql(

        "SELECT Id, DateAdded, Latitude, Longitude " +
        "FROM dbo.PointsOfInterest " +
        "WHERE geography::Point(@p0, @p1, 4326).STDistance(Location) <= @p2",

Also it appears that FromSql does not currently support named parameters, so you'll need to use @p0, @p1 etc.

11/30/2016 4:28:27 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow