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?
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.