Support value conversions for Spatial datatypes and UDTs

[mojtabakaviani]

value conversions very good feature in EF Core but not convert spatial datatypes or binary to custom objects. for example, I just save Point as geography to database and want use own Point class for geography not use NTS suites that is 621k size. other example I want save and read model class fields as binary or mapping json string to class fields.

if value conversions support spatial datatypes such as Geography/Geometry, Hierarchyid and UDTs can save and read any data types to databases and mapping to custom filed classes.

[ajcvickers]

@mojtabakaviani As far as I am aware, there is nothing that prevents you from using value conversions with any of these types. What issues have you seen?

[mojtabakaviani]

At now have problem conversation spatial data types or binary to object and UDTs. Support of NTS suites very difficult, but if can these convertions and combine with function mapping very easy can conversation and mapping any types with ef core.

[ajcvickers]

@bricelam Is it possible/easy for applications to add value converters that will read and convert the underlying binary for UDTs?

[bricelam]

Possible, but not easy. To bypass NTS, you'd need to duplicate most of the logic inside NetTopologySuite.IO.SqlServerBytes. Which itself is based on the MS-SSCLRT spec.

[mojtabakaviani]

@bricelam Exactly, developers not like limitation, ef core must for starter to professional developers. If want have custom implementation allow it.

[bricelam]

Currently, you need to provide an implementation of RelationalTypeMapping to get access to the underlying bytes since they require custom calls to SqlDataReader and SqlParamter:

var bytes = dataReader.GetSqlBytes(columnOrdinal).Value;

var parameter = command.Parameters
    .AddWithValue(parameterName, new SqlBytes(bytes));
parameter.SqlDbType = SqlDbType.Udt;
parameter.UdtTypeName = "geography";

We should provide APIs that make working with UDT bytes easier.

[kfrancis]

Here's an example:

Point patientLocation = new Point(44.218356d, -76.4685585d) { SRID = 4326 };

var pharmacies = _dbContextProvider.GetDbContext().Pharmacies
    .Where(x => x.Location.IsWithinDistance(patientLocation, 20000))
    .OrderBy(x => x.Location.Distance(patientLocation))
    .Take(10)
    .ToList();

Where I get this query running on sdk 5.0.100:

DECLARE @__p_1 int = 10,
        @__patientLocation_0 sql_variant = 'System.Data.SqlTypes.SqlBytes';

SELECT TOP(@__p_1) [p].[Id], [p].[Address1], [p].[Address2], [p].[Address3], [p].[City], [p].[CreationTime], [p].[CreatorUserId], [p].[DeleterUserId], [p].[DeletionTime], [p].[EmailAddress], [p].[FaxNumber], [p].[IsDeleted], [p].[LastModificationTime], [p].[LastModifierUserId], [p].[Latitude], [p].[Location], [p].[Longitude], [p].[PharmacyName], [p].[PharmacyStatus], [p].[PhoneExt], [p].[PhoneNumber], [p].[PostalCode], [p].[Province]
FROM [Pharmacies].[Pharmacy] AS [p]
WHERE [p].[Location].STDistance(@__patientLocation_0) <= 20000.0E0
ORDER BY [p].[Location].STDistance(@__patientLocation_0);

but what I'm expecting is:

DECLARE @__p_1 int = 10,
        @__patientLocation_0 GEOGRAPHY = geography::Point('44.218356', '-76.4685585', 4326);

SELECT TOP(@__p_1) [p].[Id], [p].[Address1], [p].[Address2], [p].[Address3], [p].[City], [p].[CreationTime], [p].[CreatorUserId], [p].[DeleterUserId], [p].[DeletionTime], [p].[EmailAddress], [p].[FaxNumber], [p].[IsDeleted], [p].[LastModificationTime], [p].[LastModifierUserId], [p].[Latitude], [p].[Location], [p].[Longitude], [p].[PharmacyName], [p].[PharmacyStatus], [p].[PhoneExt], [p].[PhoneNumber], [p].[PostalCode], [p].[Province]
FROM [Pharmacies].[Pharmacy] AS [p]
WHERE [p].[Location].STDistance(@__patientLocation_0) <= 20000.0E0
ORDER BY [p].[Location].STDistance(@__patientLocation_0);

Or at least that @__patientLocation_0 is set to a value like '0xE6100000010CC3F5285C8F424A408FC2F5285CCF2A40' and not sql_variant = 'System.Data.SqlTypes.SqlBytes';

[ajcvickers]

@kfrancis To get this on .NET core would require that SQL Server add native support for spatial types. This is tracked by https://github.com/dotnet/SqlClient/issues/30. EF works around this limitation by parsing the binary directly, which is why at the SQLClient level you just see SqlBytes.

[mojtabakaviani]

For support this conversions, need RawConvertor class with type mapping:

var converter = new RawConverter<Point, byte[]>(
    v => v.ToArray(),
    v => Point.Parse(v),
    new SqlParameter() { SqlDbType= SqlDbType.Udt, UdtTypeName= "geography" });

builder.Entity<Pharmacy>()
           .Property(p => p.Locaiton)
           .HasConversion(converter);

[andriysavin]

@mojtabakaviani this seems to be similar or related to this issue https://github.com/dotnet/efcore/issues/13736. Maybe you'll find something useful for your problem there.