How to insert DbGeography to SQL Server using dapper

Question

I've created the model using System.Data.Entity.Spatial;

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

Inserting to DB

using (SqlConnection conn = SqlHelper.GetOpenConnection())
{
    const string sql = "INSERT INTO Stores(Name, Address, Location) " + 
                       "VALUES (@Name, @Address, @Location)";
    return conn.Execute(sql, store);                                
}

i get the exception type System.Data.Entity.Spatial.DbGeography cannot be used as a parameter value

I've tried searching for ways to insert, this is the best i can find, but it's trying to insert only 1 parameter, what should i do in order to insert an object that have a dbgeography member?

Update #1

I've gave up trying to hack around or extend stuff, as i'm very new to dapper and time is not currently on my side. I went back to basic doing this, as i'm not required to do geography data type insertion very frequently

using (SqlConnection conn = SqlHelper.GetOpenConnection())
        {
            var sql = "INSERT INTO Stores (Name, Address, IsActive, Location, TenantId) " +
                      "VALUES('@Name', '@Address', @IsActive, geography::Point(@Lat,@Lng, 4326), @TenantId);";

            return conn.Execute(sql, new 
            { 
                Name = store.Name, 
                Address = store.Address, 
                IsActive = store.IsActive,
                Lat = store.Location.Latitude.Value,
                Lng = store.Location.Longitude.Value,
                TenantId = store.TenantId
            });             
        }

Kobold

Answer 1

Adding direct support for types outside of the core ADO.NET assemblies is problematic, as it either forces lots of name-based reflection, or swells the dependencies (and causes versioning problems). It is not necessary (or even appropriate, IMO) to use IDynamicParameters here - instead, ICustomQueryParameter can be used to represent a single parameter. There is no existing special-case detection of DbGeography, so unless there are library changes, you would have to do something like:

return conn.Execute(sql, new {
    store.Name, store.Address, Location=store.Location.AsParameter()
});

where AsParameter() is an extension method that returns an ICustomQueryParameter implementation that adds it appropriately.

---

Edit: see here for updates on this: https://mcmap.net/q/1916870/-query-spatial-data-with-dapper

Hardner

i've updated the question and have the 2nd part of the problem, which i realise is as problematic, which is to query the data out from db and binding it back to mvc #24406145 – Kobold