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 服务器.

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.

这是我的数据上下文

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

}

兴趣点模型

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)

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

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屋！