查询与短小精悍的空间数据 [英] Query spatial data with dapper

问题描述

我找到了一些相关的问题，但笔者放弃了，并径自使用存储过程做映射。

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

这实际上是从的此处

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

小巧玲珑的不理解空间数据，并作为许多人说，它不是， 'T作者的初衷，以支持供应商的特定实现。但文档延长查询< T> 的支持是很难找到的。

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

推荐答案

我有一个探索这个这里，其下述测试通过：

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

它的看起来的可行性，但我没有点缀每次我和跨越每个T只是还没有。欢迎您与本地提交实验 - 我很想反馈

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.

这篇关于查询与短小精悍的空间数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！