Mapping SqlGeography with Dapper

Question

I have entity "Point", that contains Id, Text and geography coordinates.

CREATE TABLE [Point] (
    [Id] INT IDENTITY CONSTRAINT [PK_Point_Id] PRIMARY KEY,
    [Coords] GEOGRAPHY NOT NULL,
    [Text] NVARCHAR(32) NOT NULL,
    [CreationDate] DATETIME NOT NULL,
    [IsDeleted] BIT NOT NULL DEFAULT(0)
)

CREATE PROCEDURE [InsertPoint]
    @text NVARCHAR(MAX),
    @coords GEOGRAPHY
AS BEGIN
    INSERT INTO [Point](Text, Coords, CreationDate)
    VALUES(@text, @coords, GETUTCDATE())    
    SELECT * FROM [Point] WHERE [Id] = SCOPE_IDENTITY()
END

This is ts sql code of table and stored procedure of inserting. I have class for using dapper :

public class DapperRequester : IDisposable {
    private readonly SqlConnection _connection;
    private SqlTransaction _transaction;

    public DapperRequester(string connectionString) {
        _connection = new SqlConnection(connectionString);
        _connection.Open();
    }
    public void Dispose() {
        _connection.Close();
    }

    public void BeginTransaction() {
        _transaction = _connection.BeginTransaction();
    }
    public void CommitTransaction() {
        _transaction.Commit();
    }
    public void RollbackTransaction() {
        _transaction.Rollback();
    }

    public void Query(string query, object parameters = null) {
        Dapper.SqlMapper.Execute(_connection, query, parameters, transaction: _transaction);
    }

    public void QueryProc(string procName, object parameters = null) {
        Dapper.SqlMapper.Execute(_connection, procName, parameters, commandType: CommandType.StoredProcedure, transaction: _transaction);
    }

    public IEnumerable<T> Execute<T>(string query, object parameters = null) {
        return Dapper.SqlMapper.Query<T>(_connection, query, parameters, transaction: _transaction);
    }

    public IEnumerable<dynamic> ExecuteProc(string procName, object parameters = null) {
        return Dapper.SqlMapper.Query(_connection, procName, parameters,
                                         commandType: CommandType.StoredProcedure, transaction: _transaction);
    }

    public IEnumerable<T> ExecuteProc<T>(string procName, object parameters = null) {
        return Dapper.SqlMapper.Query<T>(_connection, procName, parameters,
                                         commandType: CommandType.StoredProcedure, transaction: _transaction);
    }
}

c#-class is :

public class Point
{
    public int Id { get; set; }
    public SqlGeography Coords { get; set; }
    public string Text { get; set; }
}

And repository has method

public Point InsertPoint(string text, SqlGeography coords)
    {
        using (var requester = GetRequester())
        {
            return requester.ExecuteProc<Point>("InsertPoint", new { text, coords }).FirstOrDefault();
        }
    }

When I use such system for any other class, everything is okey, but there is a problem with mapping, I think it is because of SqlGeography type.. Using :

SqlGeography coords = new SqlGeography();
        coords = SqlGeography.Point(10.5, 15.5, 4326);
        Point point = new Point { Coords = coords, Text = "Text" };
        point = Repositories.PointRepository.InsertPoint(point.Text, point.Coords);

And I have an exception The member coords of type Microsoft.SqlServer.Types.SqlGeography cannot be used as a parameter value

Is there some secret of mapping that type?

Answer 1

I ran into a similar problem. I found that Dapper would map resultant fields to Microsoft.SqlServer.Types.SqlGeography just fine, but using them as parameters didn't work.

I've modified the SqlMapper.cs file to include support for this type. You can see the Gist here: https://gist.github.com/bmckenzie/4961483

Click on "Revisions" to see what I changed.

Answer 2

Dapper 1.32 now includes direct support for this. Your code should now simply work.

Answer 3

Dapper does not support DB Provider specific data types. In your case its GEOGRAPHY.

Dapper has no DB specific implementation details, it works across all .net ado providers including sqlite, sqlce, firebird, oracle, MySQL and SQL Server

In order to handle this param with Dapper, you would have to write your own handling for it. For an example, see this answer.

Good luck