DbGeography with MySQL and EntityFramework

Question

I am using DbGeography from System.Data.Entity.Spatial; and Entity Framework with SQL Server Databases. Now I switch to use MySQL server and I am getting an error when I am creating the database related to the DbGeography type.

How can I fix this issue without change all my domain class property to another type?

public class Place
{
  ...
    public virtual int Id { get; set; }
    public string Name { get; set; }
    public DbGeography Location {get;set;}
  ...

}

The error that I am having is this one:

System.NotSupportedException: There is no store type corresponding to the EDM type 'Edm.Geography' of primitive type 'Geography'.

Answer 1

I have gone through a bunch of MySQL documentations to find out that mysql 5.x currently does not support DbGeography data type.

For mysql 5.x only DbGeometry is supported. I am frustrated.

refer to the document MySQL Official Document about Spatial Data Support

Entity Framework support two main types for spatial data: DbGeometry and DBGeography. The second one is NOT supported at Connector/Net since the MySQL server doesn't have any equivalent type to which map this type in. So all the examples will use the DbGeometry type.

Maybe you can use DbGeometry data type POINT to save longitude and latitude, then use Haversine formula to calculate the distance.

Answer 2

As pointed out by ehe888, MySQL Connector/Net does not support mapping to DbGeography, but is supports mapping to DbGeometry.

If you just need to store a POINT (the only geometry supported by MySQL Connector/Net as of version 6.10), then you can easily do the conversion yourself between DbGeography and DbGeometry in your entity:

    [Column("gps_location")]
    public DbGeometry LocationGeometry { get; set; }

    [NotMapped] // Mapping to DbGeography is not supported by MySQL Connector/Net, see https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework50.html
    public DbGeography Location
    {
        get => LocationGeometry != null ? DbGeography.FromBinary(LocationGeometry.AsBinary()) : null;
        set => LocationGeometry = value != null ? DbGeometry.FromBinary(value.AsBinary()) : null;
    }