Integration with EfCore.NetTopologySuite

[SomePrettyUsername]

Hello! I want to use spatial data in my app and i found nothing about integration with NetTopologySuite. Do you have any plans about this feature? Or this feature exists out of the box and not documented?

If i try code like this, exception thrown:

class Point  
{  
    [Key]  
    public int Id { get; set; }
    public Geometry GeoPoint { get; set; }
}

    Geometry border = GetNtsBorder();
    List<Station> = contexts.PointDbSet.ToLinqToDBTable()
        .TableName("specificPointTable")
        .Where(x => border.Contains(x.GeoPoint)) //or intersects or something else
        .ToList();

System.ArgumentException: No mapping exists from object type NetTopologySuite.Geometries.LinearRing to a known managed provider native type. at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed) at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromType(Type dataType) at Microsoft.Data.SqlClient.SqlParameter.GetMetaTypeOnly() at Microsoft.Data.SqlClient.SqlParameter.get_SqlDbType() at lambda_method(Closure , IDbDataParameter ) at LinqToDB.DataProvider.SqlServer.SqlServerDataProvider.SetParameter(DataConnection dataConnection, IDbDataParameter parameter, String name, DbDataType dataType, Object value) at LinqToDB.Data.DataConnection.QueryRunner.AddParameter(DataConnection dataConnection, ICollection1 parms, String name, SqlParameter parm) at LinqToDB.Data.DataConnection.QueryRunner.GetParameters(DataConnection dataConnection, PreparedQuery pq) at LinqToDB.Data.DataConnection.QueryRunner.SetQuery(DataConnection dataConnection, IQueryContext queryContext, Int32 startIndent) at LinqToDB.Data.DataConnection.QueryRunner.SetQuery() at LinqToDB.Linq.QueryRunnerBase.SetCommand(Boolean clearQueryHints) at LinqToDB.Data.DataConnection.QueryRunner.ExecuteReader() at LinqToDB.Linq.QueryRunner.ExecuteQuery[T](Query query, IDataContext dataContext, Mapper1 mapper, Expression expression, Object[] ps, Object[] preambles, Int32 queryNumber)+MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Thank you in advance!

[sdanyliv]

Not by default but it can be "easily" enabled.

You have to define mapping between SqlGeometry and IGeometry (or Geometry) types. Not tested, but something like this

ONLY ONCE PER APPLICATION

var mapping = MappingSechema.Default;

// to database
var geoWriter = new MsSql2008GeometryWriter();
mapping.SetConverter<IGeometry, DataParameter>(g =>
{
	new DataParameter("p", geoWriter.WriteGeometry(g), DataType.Udt)
});

// from database
var geoReader = new MsSql2008GeometryReader();
mapping.SetConverter<SqlGeometry, IGeometry>(sg =>
{
	return geoReader.Read(sg);
});

[SomePrettyUsername]

Thank you for answer, but not working unfortunately. Same exception. So, i have several questions.

1. Can i use any WkbWriter to serialize data in conversion to DataParameter? Like this:

    var writer = new SqlServerBytesWriter(); //or any else wkbWriter, not MsSql2008GeometryWriter
    schema.SetConverter<Geometry, DataParameter>((geom) =>
    {
          var val1 = writer.Write(geom);
          return new DataParameter("p", val1, DataType.Udt);
    });

2. Can i easily avoid SqlTypes using while read spatial data? Something like this maybe?

    var reader = new SqlServerBytesReader(); //or any else wkbReader
    schema.SetConverter<byte[], Geometry>((sqlGeomInBytes) => 
    {
          var geometry = reader.Read(sqlGeomInBytes);
          return geometry;
    });

[sdanyliv]

1. Possible, i have no experience with SqlServer Geospatial types conversions and which classes has to be used for conversion.
2. It may work, but in this case you have specified conversion of all bytes[] to Gospatial. Better to specify which DbType expected, there is an overload for SetConverter.

[SomePrettyUsername]

Hello again! Thank you for your previous answers!

Class MsSql2008GeometryWriter is located in package NetTopologySuite.IO which is old and not working with NTS in efcore package. So, i decide to construct SqlGeometry/SqlGeography "by hands", and have exception again No mapping exists from object type Microsoft.SqlServer.Types.SqlGeometry to a known managed provider native type.. Have you any ideas why it can happen?

LinqToDBForEFTools.Initialize();
SqlServerTools.ResolveSqlTypes(typeof(SqlGeography).Assembly);
SqlServerTools.ResolveSqlTypes(typeof(SqlGeometry).Assembly);

var mapping = MappingSchema.Default;
mapping.SetConverter<Geometry, DataParameter>(g =>
{
      var sqlGeography = SqlGeography.Parse(g.ToString());
      return new DataParameter("p", sqlGeography, DataType.Udt);
});

The next decision was to use wellknown binary, but when query constructed, the wkb parameter had the binary type, even if I specified dbType. How can i setup parameter type?

LinqToDBForEFTools.Initialize();
SqlServerTools.ResolveSqlTypes(typeof(SqlGeography).Assembly);
SqlServerTools.ResolveSqlTypes(typeof(SqlGeometry).Assembly);

var mapping = MappingSchema.Default;
mapping.SetConverter<Geometry, DataParameter>(g =>
{
      var wkb = new SqlServerBytesWriter().Write(g);
      return new DataParameter("p", wkb, DataType.Udt, "geography");
});

Result query

exec sp_executesql N'SELECT
	[x].[Field1],
	[x].[Field2],
	[x].[GeoPoint]
FROM
	[SpecificPointTables] [x]
WHERE
	STIntersects(@targetPolygon) //here error because of @targetPolygon type, must be  @targetPolygon.STIntersects(x.GeoPoint)
',
N'@targetPolygon varbinary(8000),
@targetPolygon=0xE6100000010029000000D670E66C718572408CC0B41762284BC000000000002072400000000000C04CC02A8F19938EBA71408CC0B41762284BC0FE28F88B6B3F714084A6AD528A8849C05DE9C092D2AC7040944DE68A960E48C02AFEC07BF504704050A71629CCFD46C00000000000A06E4074D6C9F5279946C0AD037E0815366D4050A71629CCFD46C0462D7EDA5AE66B40944DE68A960E48C004AE0FE828C16A4084A6AD528A8849C0ACE1CCD9E2CA69408CC0B41762284BC000000000000069400000000000C04CC0541E33261D35684084C0B41762284BC0FD51F017D73E674084A6AD528A8849C0BAD28125A51966409C4DE68A960E48C054FC81F7EAC9644050A71629CCFD46C0000000000060634074D6C9F5279946C0AD037E0815F6614050A71629CCFD46C0462D7EDA5AA660409C4DE68A960E48C0065C1FD051025F4084A6AD528A8849C057C399B3C5155D408CC0B41762284BC00000000000805B400000000000C04CC0A93C664C3AEA59408CC0B41762284BC0FBA3E02FAEFD574084A6AD528A8849C073A5034B4AB35540944DE68A960E48C0A7F803EFD513534050A71629CCFD46C0000000000040504074D6C9F5279946C0B00EF82154D84A4050A71629CCFD46C018B5F8696B994540944DE68A960E48C00AB83EA0A304414084A6AD528A8849C05A0D67CE16573A408CC0B41762284BC0FCFFFFFFFFFF33400000000000C04CC040E53163D2512B408CC0B41762284BC0903F0AFEE2DA174084A6AD528A8849C05DE9C092D24C76409C4DE68A960E48C02AFEC07BF5A4754050A71629CCFD46C00000000000F0744074D6C9F5279946C0D6013F840A3B744050A71629CCFD46C0A3163F6D2D9373409C4DE68A960E48C002D707749400734084A6AD528A8849C0D670E66C718572408CC0B41762284BC001000000020000000001000000FFFFFFFF0000000003 

And the last, i tried to use query interceptors to change query parameter or query, but interceptors not invoking on linq2db queries. May be you have any ideas why interceptors not called? All DbCommandInterceptor virtual methods was overriden and interceptor worked well on efcore queries.

[MaceWindu]

I will close it, please open new issue if this is still actual.

Some notes:

• linq2db use own interceptor which you can register with AddInterceptor configuration API
• there is no need to modify global MappingSchema.Default with new AddMappingSchema configuration API in latest release: you can configure separate mapping schema