Query spatial data with dapper

Question

I've found some related questions, but the author gave up and went ahead with using stored procedures to do the 'mapping'.

This is actually a continuation question from here

Model

public class Store
{
    public int Id { get; private set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public DbGeography Location { get; set; }
}

Querying

using (SqlConnection conn = SqlHelper.GetOpenConnection())
{
    const string sql = "Select * from Stores";
    return conn.Query<Store>(sql, new { Tenant_Id = tenantId });
}

Dapper doesn't understand spatial data, and as many had said, it wasn't the authors original intention to support vendor specific implementations. But the documentation to extend the Query<T> support is hard to find

Renaterenato

It isn't so much that it is hard to find, but rather that it isn't a feature. It could be, but it would need to be scoped, designed, written, tested, documented and deployed. I'm all up for ideas / contributions... – Black

Just curious, are you one of the contributor to dapper? I understand that dapper's main goals are simplicity and speed, but i really hope that it can have a modular/plug-in options for situations like this, sort of like what jquery is doing. – Renaterenato

Yes. And it does seem like maybe dapper could simply adopt a "well, hopefully it'll work" approach to anything it doesn't recognise - and just add it as a value without any additional processing (or cast it from GetValue, and just assume it'll work). I'm willing to take a look at that, but: it isn't something that it does today. – Black

Great! Can you make a spatial sample and i promise i'll try to commit at least the docs – Renaterenato

Just wondering, can you point me the direction to make this work? Is there any interface for custom parsing? – Renaterenato

I've just done a quick test, and no: the "well, hopefully it'll work" approach - doesn't: "No mapping exists from object type System.Data.Entity.Spatial.DbGeography to a known managed provider native type." (from inside SqlCommand / TdsParser). No, there isn't currently an interface for custom parsing: that is all part of the "would need to be scoped, designed, written, tested, ..." – Black

which begs the question: if this was raw ADO.NET: what would you do to make it work? Would you use SqlGeography from Microsoft.SqlServer.Types.dll ? – Black

I've started of with SqlGeography, but it doesn't work with code-first migration with mvc5. – Renaterenato

There's a dbgeography.PointFromText which I think might help – Renaterenato

Answer 1

I have an exploration into this here, for which the following test passes:

class HazGeo
{
    public int Id { get;set; }
    public DbGeography Geo { get; set; }
}
public void DBGeography_SO24405645_SO24402424()
{
    global::Dapper.SqlMapper.AddTypeHandler(typeof(DbGeography), new GeographyMapper());
    connection.Execute("create table #Geo (id int, geo geography)");

    var obj = new HazGeo
    {
        Id = 1,
        Geo = DbGeography.LineFromText("LINESTRING(-122.360 47.656, -122.343 47.656 )", 4326)
    };
    connection.Execute("insert #Geo(id, geo) values (@Id, @Geo)", obj);
    var row = connection.Query<HazGeo>("select * from #Geo where id=1").SingleOrDefault();
    row.IsNotNull();
    row.Id.IsEqualTo(1);
    row.Geo.IsNotNull();
}

class GeographyMapper : Dapper.SqlMapper.TypeHandler<DbGeography>
{
    public override void SetValue(IDbDataParameter parameter, DbGeography value)
    {
        parameter.Value = value == null ? (object)DBNull.Value : (object)SqlGeography.Parse(value.AsText());
        ((SqlParameter)parameter).UdtTypeName = "GEOGRAPHY";
    }
    public override DbGeography Parse(object value)
    {
        return (value == null || value is DBNull) ? null : DbGeography.FromText(value.ToString());
    }
}

It looks viable, but I haven't dotted every i and crossed every t just yet. You're welcome to experiment with that commit locally - I'd love feedback.

Black

It's working perfectly on my end, and just a dumb question, i'm 'registering' the typehandler in global.asax when the application starts, is that a good place to put it? – Renaterenato

One way to improve the performance of this solution is to use well-known binary format for the conversion between SqlGeography and DbGeography instead of the well-known text, please see https://mcmap.net/q/390932/-sql-geography-to-dbgeography for an example. – Embracery

@Serge Excellent; I've yanked that into "dapper" : github.com/StackExchange/dapper-dot-net/commit/… – Black

This just saved my A$$. Thank you again, Sir Gravell !!! – Emersonemery