Mapping SqlGeography with Dapper
c#, dapper, mapping, sql-server, sqlgeography
Solution
Dapper 1.32 now includes direct support for this. Your code should now simply work.
Problem
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?