Efcore空间查询：在输入的位置9处应该有一个数字。输入具有@ p0。 -- [英] Efcore Spatial Query: A number is expected at position 9 of the input. The input has @p0. -

问题描述

我试图通过使用 DBSet.FromSQL 方法并手动滚动迁移来解决Entity Framework Core不支持空间类型这一事实。在SQL Server上添加地理列。

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.

这是我的DataContext

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模型

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; }
    }
}

迁移以添加地理类型

    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)
        {
            migrationBuilder.DropTable(
                name: "PointsOfInterest");
        }
    }
}

这是查询

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();
        }
    }
}

从控制器调用

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

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

堆栈跟踪错误为

    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.
System.FormatException:
   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
pose)

如果我d将查询参数编码到查询字符串中，查询成功完成，因此查询参数对象似乎有问题。

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.

推荐答案

我要使其正常工作的唯一方法是不使用 WKT 方法

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

即： geography :: Point（）而不是 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",
        longitude,
         latitude,
        radius).ToListAsync();
    }

此外，似乎 FromSql 当前不支持命名参数，因此您需要使用@ p0，@ p1等。

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

这篇关于Efcore空间查询：在输入的位置9处应该有一个数字。输入具有@ p0。 --的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！