dapper.fastcrud doesn't mapped geometry data from postgresql

Question

I have a spatial data in Postgresql. For example table planet_osm_point has 2 attributes:

CREATE TABLE public.planet_osm_point
(
    osm_id bigint,
    way geometry(Point,3857)
)

If I use dapper to CRUD operations all work fine. But if I use Dapper.fastCRUD then "way" property with geometry is always null

Class OsmPoint:

using NetTopologySuite.Geometries;
using System.ComponentModel.DataAnnotations.Schema; 

namespace DapperTest
{
    [Table("planet_osm_point")]
    public class OsmPoint
    {
        [Column("osm_id")]
        public long OsmId { get; set; }

        [Column("way")]
        public Point Way { get; set; }
    }
}

If I use Dapper then I receive the Way property has geometry coordinats:

using (NpgsqlConnection conn = new NpgsqlConnection(_connectionString))
{
    conn.Open();
    conn.TypeMapper.UseNetTopologySuite();
    var result = conn.Query<OsmPoint>("SELECT * FROM planet_osm_point LIMIT 5000").ToList();
    return result;
}

But if I use Dapper.fastCRUD then the Way always is null

using (NpgsqlConnection conn = new NpgsqlConnection(_connectionString))
{
    conn.Open();
    conn.TypeMapper.UseNetTopologySuite();
    var result = conn.Find<OsmPoint>(p=>p.Top(5000));
    return result;
}

Does anyone know how to make Dapper.fastCRUD work with geometric data?

Long Ngô Thành · Answer 1 · 2018-10-18T04:15:51.287

0

First you create TypeHandler for Geometry type like this:

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

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

        throw new ArgumentException();
    }
}

add typehandler:

SqlMapper.AddTypeHandler(new GeometryTypeMapper());

set property before query by FastCURD :

OrmConfiguration.GetDefaultEntityMapping<OsmPoint>().SetProperty(p => p.way, prop => prop.SetDatabaseColumnName("way"));

edited Oct 18 '18 at 04:15

answered Oct 15 '18 at 11:15

Long Ngô Thành

1
1
2

Yes, this code is needed for the Dapper to mapping the Geometry data. But Dapper.FastCRUD ignores Geometry properties for building query. In this case FastCRUD return query: SELECT "osm_id" FROM "planet_osm_point". This query doesn't contains the "way" attribute. – Dmitry Evstakhov Oct 19 '18 at 06:41
1

default Dapper.FastCURD will ignores geometry attribute so you will get null value for geometry property so you must re-define it by SetProperty like above before query or update. – Long Ngô Thành Oct 20 '18 at 09:56
There is an article in the wiki [link](https://github.com/MoonStorm/Dapper.FastCRUD/wiki/Default-library-conventions). But I saw it too late. Everything is explained there =) – Dmitry Evstakhov Oct 20 '18 at 15:29

score 0 · Accepted Answer · answered Oct 19 '18 at 07:03

Dapper.FastCRUD as default use only symple sql types to build queries. You can see it in Dapper.FastCrud.Configuration.OrmConventions.cs:

public virtual IEnumerable<PropertyDescriptor> GetEntityProperties(Type entityType)
{
    return TypeDescriptor.GetProperties(entityType)
        .OfType<PropertyDescriptor>()
        .Where(propDesc => 
            !propDesc.Attributes.OfType<NotMappedAttribute>().Any()
            && !propDesc.IsReadOnly 
            && propDesc.Attributes.OfType<EditableAttribute>().All(editableAttr => editableAttr.AllowEdit)
            && this.IsSimpleSqlType(propDesc.PropertyType));
}

I override the IsSimpleSqlType(Type propertyType) method:

public class GeometryConvention: OrmConventions
{
    protected override bool IsSimpleSqlType(Type propertyType)
    {
        var res = base.IsSimpleSqlType(propertyType) || propertyType.BaseType != null && propertyType.BaseType.Name.StartsWith("geometry", StringComparison.OrdinalIgnoreCase);
        return res;
    }
}

Register custom convention:

OrmConfiguration.Conventions = new GeometryConvention();

And use custom GeometryTypeMapper such as in Long Ngô Thành answer.

dapper.fastcrud doesn't mapped geometry data from postgresql

2 Answers2