C# MySql Dapper MySqlGeometry

Question

I am trying to get the Polygon as MySqlGeometry using Dapper.
But the MySql connector understands only MySqlGeometry as Point.
And I need the other types also, like Polygon.
This is what I got from other forums:

// 1. Add Dapper custom Type handler.
Dapper.SqlMapper.AddTypeHandler(new MySqlGeometryTypeHandler());    

// 2. Implement that custome handler.
public class MySqlGeometryTypeHandler : SqlMapper.TypeHandler<MySqlGeometry>
{
    public override MySqlGeometry Parse(object value) { return new MySqlGeometry(MySqlDbType.Geometry, (byte[])value); }
    public override void SetValue(System.Data.IDbDataParameter parameter, MySqlGeometry value) { parameter.Value = value; }
}

// 3. Here is the Data class
public class Geo
{ 
    public int Id { get; set; } 
    public MySqlGeometry G { get; set; }
}

// 4. Here is the Dapper query
List<Geo> datas = Dapper.SqlMapper.Query<Geo>(cnn, "select * from geo;").ToList();

How do I get the Polygon rows I have in the 'geo' table ?

Answer 1

It comes out - MySqlGeometry doesn't support (hopefully yet) other types than Point.
So the solution I got is:

1. Use instead of MySqlGeometry the Microsoft's System.Data.Spatial.DbGeography in the model.

    public class Geo
    {
        public int Id { get; set; }
        public DbGeography G { get; set; }
    }

2. Change the custom Type handler and registration accordingly:

    public class DbGeographyTypeHandler : SqlMapper.TypeHandler<DbGeography>
    {
        public override DbGeography Parse(object value) { return DbGeography.FromBinary((byte[])value); }
        public override void SetValue(IDbDataParameter parameter, DbGeography value) { parameter.Value = value.AsBinary(); }
    }

Dapper.SqlMapper.AddTypeHandler<DbGeography>(new DbGeographyTypeHandler());

3. And change the sql to return the value as standard WKB, which Microsoft's DbGeography understands. You can also use st_asbinary instead of st_aswkb - both worked for me.

List<Geo> datas = Dapper.SqlMapper.Query<Geo>(conn, "select id, st_aswkb(g) g from geo;").ToList();

4. Just to note:

• The MySql is 8, and the Dapper is 2.0.78.
• I used DbGeography - because I need earth geo locations, as opposed to DbGeometry.
• I used the MySql column datatype as Geometry, because I need spatial index on it for faster data retrieval.
• If you need spatial index, make sure you set your Geometry column as non null, and with srid (e.g. 4326), because default srid 0 will cause your query to ignore the spatial index.