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 ?

Daniel Gordon · Accepted Answer · 2021-04-15 17:55:19Z

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

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

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

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

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.

Collectives™ on Stack Overflow

C# MySql Dapper MySqlGeometry

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related