Got NotSupportedException when using Dapper to call a PostgreSQL function with a geography argument

Question

I was using Dapper (using npgsql data provider with NetTopologySuite plugin) to call a PostgreSQL function with a geography argument then received a NotSupportedException:

System.NotSupportedException: The member _location of type NetTopologySuite.Geometries.Point cannot be used as a parameter value
at Dapper.SqlMapper.LookupDbType(Type type, String name, Boolean demand, ITypeHandler& handler) in C:\projects\dapper\Dapper\SqlMapper.cs:line 417
at Dapper.SqlMapper.CreateParamInfoGenerator(Identity identity, Boolean checkForDuplicates, Boolean removeUnused, IList`1 literals) in C:\projects\dapper\Dapper\SqlMapper.cs:line 2516
at Dapper.SqlMapper.GetCacheInfo(Identity identity, Object exampleParameters, Boolean addToCache) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1707
at Dapper.SqlMapper.ExecuteScalarImplAsync[T](IDbConnection cnn, CommandDefinition command) in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 1207
...

But it works fine when I use NpgsqlCommand with type specified via AddWithValue method.

How can I make Dapper map NetTopologySuite.Geometries.Point to geography?

score 7 · Accepted Answer · answered Jul 08 '18 at 03:34

After doing some search I found an unaccepted answer that solved my problem.

Make sure I added Npgsql.NetTopologySuite package and its mapper is enabled connection.TypeMapper.UseNetTopologySuite(); then the ADO.Net command works fine.

I added a custom Dapper.SqlMapper.TypeHandler:

public class GeographyTypeMapper : SqlMapper.TypeHandler<Geometry> {
    public override void SetValue(IDbDataParameter parameter, Geometry value) {
        if (parameter is NpgsqlParameter npgsqlParameter) {
            npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Geography;
            npgsqlParameter.NpgsqlValue = value;
        } else {
            throw new ArgumentException();
        }
    }

    public override Geometry Parse(object value) {
        if (value is Geometry geometry) {
            return geometry;
        } 

        throw new ArgumentException();
    }
}

Then use it SqlMapper.AddTypeHandler(new GeographyTypeMapper()); and everything works fine.

Another example: https://medium.com/dapper-net/custom-type-handling-4b447b97c620 — Alireza Sattari, May 30 '22 at 19:37

score 0 · Answer 2 · answered Jul 07 '18 at 21:07

0

I'd you just upgraded to Npgsql 4, you will need to uses one of the spatial plugins. Read [the release notes] (http://www.npgsql.org/doc/release-notes/4.0.html#improved-spatial-support-postgis).

answered Jul 07 '18 at 21:07

Shay Rojansky

15,357
2
40
69

I managed to map `Point` to `geography` using `NetToologySuite` plugin but Dapper didn't understand the mapping. I also edited my question to make it clearer. – phqb Jul 08 '18 at 02:34

Got NotSupportedException when using Dapper to call a PostgreSQL function with a geography argument

2 Answers2