c# - 将几何转换为地理的实体框架

Question

我试图通过我的数据库中的实体类插入几何对象，但总是返回错误“地理与几何不兼容”。我最初收到一个字符串，传递几何坐标的 X 和 Y 值，并进行必要的转换以创建 Geometry 提供的 Point 类型，我不需要进行任何计算，因此 Geometry 类型最适合我立即使用。我使用下面的函数进行转换：

public static Geometry ConvertToGeometry(string coordinatesAux)
{
    if(coordinatesAux == null)
        throw new NullReferenceException();

    NumberFormatInfo formatProvider = new NumberFormatInfo();
    formatProvider.NumberGroupSeparator = ".";
        
    var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
    var currentLocation = geometryFactory.CreatePoint(new Coordinate(
                                                           Convert.ToDouble(coordinatesAux.Substring(0, coordinatesAux.IndexOf(",")), formatProvider), 
                                                           Convert.ToDouble(coordinatesAux.Substring(coordinatesAux.IndexOf(",") + 1), formatProvider)));
        
    return currentLocation;                                                            
}

我还在 Startup.cs 中对 db 进行了必要的修改以使用 NetTopologySuite，但是当我尝试通过 API 插入值时，它返回最初描述的错误。我不知道问题是否在于我将字符串转换为几何的方式，或者是否是实体本身将该字段识别为地理点。

我尝试传递的值是 -20.338113、-40.287893 和我使用的实体类：

public class Address : EntityBase<int>
{
    public Address(string district, string street, int number, string complement, string zipCode, 
    string cityDescription, string stateDescription, Geometry coordinates, int countryId, byte stateId, int cityId)
    {
        District = district;
        Street = street;
        Number = number;
        Complement = complement;
        ZipCode = zipCode;
        CityDescription = cityDescription;
        StateDescription = stateDescription;
        Coordinates = coordinates;
        CountryId = countryId;
        StateId = stateId;
        CityId = cityId;
    }

    public string District { get; set; }
    public string Street { get; set; }
    public int Number { get; set; }
    public string Complement { get; set; }
    public string ZipCode { get; set; }
    public string CityDescription { get; set; }
    public string StateDescription { get; set; }
    public virtual Geometry Coordinates { get; set; }
    public int CountryId { get; set; }
    public byte StateId { get; set; }
    public int CityId { get; set; }
    public int? CustomerId { get; set; }
    public int? StoreId { get; set; }
    public int? ProfessionalId { get; set; }
}

所以，再次问，是实体框架做的事情还是我犯了错误？如果需要更多代码部分，我将编辑要添加的问题。

---

编辑 1

表地址：

CREATE TABLE Address(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    District VARCHAR(50) NOT NULL, -- Bairro
    Street VARCHAR(100) NOT NULL, -- Rua
    --Description VARCHAR(100) NOT NULL,
    Number INT,
    Complement VARCHAR(100),
    ZipCode VARCHAR(20) NOT NULL,
    CityDescription VARCHAR(100),
    StateDescription VARCHAR(100),
    Coordinates GEOMETRY,
    
    CountryId INT FOREIGN KEY REFERENCES Country(Id) NOT NULL,
    StateId TINYINT FOREIGN KEY REFERENCES State(Id),
    CityId INT FOREIGN KEY REFERENCES City(Id),
    CustomerId INT FOREIGN KEY REFERENCES Customer(Id),
    StoreId INT FOREIGN KEY REFERENCES Store(Id),
    ProfessionalId INT FOREIGN KEY REFERENCES Professional(Id),

    INDEX IndexAddressCountryId NONCLUSTERED (CountryId),
    INDEX IndexAddressStateId NONCLUSTERED (StateId),
    INDEX IndexAddressCityId NONCLUSTERED (CityId),
    INDEX IndexAddressCustomerId NONCLUSTERED (CustomerId),
    INDEX IndexAddressStoreId NONCLUSTERED (StoreId),
    INDEX IndexAddressProfessionalId NONCLUSTERED (ProfessionalId)
)

标签： c#entity-frameworkspatialrdbmsef-core-3.1

Answer 1

这是一个工作正常的示例，映射到geometry或geography。你必须看看你在做什么不同。（顺便说一句，您可能想要geography在geometrySQL Server 中是一个平坦的欧几里得空间，并且不会为您提供准确的距离计算或纬度和经度坐标的直线）。

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Linq;
using System.Security.Cryptography;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using NetTopologySuite.Geometries;


namespace EfCore3Test
{

    public class Address : EntityBase<int>
    {
        public Address() { }
        public Address(string district, string street, int number, string complement, string zipCode,
        string cityDescription, string stateDescription, Geometry coordinates, int countryId, byte stateId, int cityId)
        {
            District = district;
            Street = street;
            Number = number;
            Complement = complement;
            ZipCode = zipCode;
            CityDescription = cityDescription;
            StateDescription = stateDescription;
            Coordinates = coordinates;
            CountryId = countryId;
            StateId = stateId;
            CityId = cityId;
        }

        public string District { get; set; }
        public string Street { get; set; }
        public int Number { get; set; }
        public string Complement { get; set; }
        public string ZipCode { get; set; }
        public string CityDescription { get; set; }
        public string StateDescription { get; set; }

        //[Column(TypeName ="geometry")]
        public virtual Geometry Coordinates { get; set; }
        public int CountryId { get; set; }
        public byte StateId { get; set; }
        public int CityId { get; set; }
        public int? CustomerId { get; set; }
        public int? StoreId { get; set; }
        public int? ProfessionalId { get; set; }
    }

    public class EntityBase<T>
    {
        public T Id { get; set; }
    }

    public class Db : DbContext
    {
           
        public Db(): base()
        {

        }

        private static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
        {
            builder.AddFilter((category, level) =>
               category == DbLoggerCategory.Database.Command.Name
               && level == LogLevel.Information).AddConsole();
        });
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var constr = "Server = localhost; database = efcore3test; integrated security = true";
            optionsBuilder.UseLoggerFactory(loggerFactory)
                          .UseSqlServer(constr, o => o.UseRelationalNulls().UseNetTopologySuite());


            base.OnConfiguring(optionsBuilder);
        }

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

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }

    }




    class Program
    {


    static void Main(string[] args)
        {
 

            using (var db = new Db())
            {

                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                var geometryFactory = NetTopologySuite.NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
                var currentLocation = geometryFactory.CreatePoint(new NetTopologySuite.Geometries.Coordinate(-122.121512, 47.6739882));

                var addr = new Address("a", "a", 1, "a", "00223", "city", "state", currentLocation, 1, 2, 3);

                db.Addresses.Add(addr);

                db.SaveChanges();

            }

        }
    }

}