Entity Framework converting Geometry into Geography

user_13713924 0
Viewed 1086

I'm trying to insert a Geometry object through an Entity class in my Database, but the error "geography is incompatible with geometry" always returns. I initially receive a string passing the X and Y values of what would be a geometrical coordinate and do the necessary conversions to create the Point type that Geometry offers, I don't need to make any calculations so Geometry type is the best for me to use right now. I use the function below to do the convertion:

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

I also made the necessary modification in Startup.cs for db to use NetTopologySuite, but when I try to insert the value through the API, it returns the error described initially. I don't know if the problem is in the way I did to convert from string to Geometry, or if it is the Entity itself that recognizes the field as a Geography Point.

The value I try to pass is -20.338113, -40.287893 and the Entity class I use:

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

So, asking again, is something that Entity framework do or I made a mistake? If more parts of the code are needed, I will edit the question to add.

Edit 1

Table Address:

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)
)
ef-core-3.1 spatial rdbms entity-framework c#
1 Answers

Here's a sample that works fine, mapping to either geometry or geography. You'll have to see what you're doing differenly. (And btw you probably want geography as geometry in SQL Server is a flat Euclidean space, and will not give you accurate distance calculations or straight lines for coordinates in latitude and longitude).

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

            }

        }
    }

}
user_72977000
Related