How to use linq2db to insert sql geography?-CodePudding

It is simple really:

var a = SqlGeography.STGeomFromText(new SqlChars("POINT(-122.34900 47.65100)"), 4326);`

and then insert

await _geoDb.Table.Value(x => x.Geom, a) // Geom is of type SqlGeography

This is giving me error:

System.ArgumentException: No mapping exists from object type Microsoft.SqlServer.Types.SqlGeography to a known managed provider native type.

I tried manual insert with geography::STGeomFromText and that works as expected.

Mapping is:

[Column("geom"), Nullable] 
public SqlGeography Geom { get; set; } // geography

Table def.

create table municipal.country
(
    id       int identity
        constraint PK_Canton
            primary key,
    geom     geography
)

I am using for Microsoft.SqlServer.Types this nuget dotMorten.Microsoft.SqlServer.Types.

Linq2Db version is: 3.1.6 (if i update to latest it is a same thing).

UPDATE

I was able to resolve this by installing dotMorten.Microsoft.SqlServer.Types 1.5 and adding that custom assembly directive into Program.cs (https://linq2db.github.io/articles/FAQ.html#how-can-i-use-sql-server-spatial-types).

It is working for simple Point but in a case of multipolygon I am getting this error.

System.InvalidOperationException: Invalid operation. The connection is closed.
   at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at System.Data.SqlClient.TdsParserStateObject.WriteBytes(ReadOnlySpan`1 b, Int32 len, Int32 offsetBuffer, Boolean canAccumulate, TaskCompletionSource`1 completion, Byte[] array)
   at System.Data.SqlClient.TdsParserStateObject.WriteByteArray(Byte[] b, Int32 len, Int32 offsetBuffer, Boolean canAccumulate, TaskCompletionSource`1 completion)
   at System.Data.SqlClient.TdsParser.TDSExecuteRPCAddParameter(TdsParserStateObject stateObj, SqlParameter param, MetaType mt, Byte options)
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.BeginExecuteNonQuery(AsyncCallback callback, Object stateObject)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)
   at System.Threading.Tasks.TaskFactory`1.FromAsync(Func`3 beginMethod, Func`2 endMethod, Object state)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at LinqToDB.Data.RetryPolicy.RetryPolicyBase.ExecuteImplementationAsync[TResult](Func`2 operation, CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.ExecuteNonQueryAsync(CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.QueryRunner.ExecuteNonQueryAsync(CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.NonQueryQueryAsync(Query query, IDataContext dataContext, Expression expression, Object[] ps, Object[] preambles, CancellationToken cancellationToken)
   at LinqToDB.Linq.QueryRunner.NonQueryQueryAsync(Query query, IDataContext dataContext, Expression expression, Object[] ps, Object[] preambles, CancellationToken cancellationToken)
   at LinqToDB.Linq.ExpressionQuery`1.LinqToDB.Async.IQueryProviderAsync.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at LinqToDB.LinqExtensions.InsertAsync[T](IValueInsertable`1 source, CancellationToken token)
   at GeoIndexer.Services.GeoIndexerService.IndexCantons(CancellationToken cancellationToken) in /Users/nikola/projects/immoledo/GeoIndexer/src/Geoindexer/Services/GeoIndexerService.cs:line 118
   at GeoIndexer.GeoIndexerConsoleApp.<>c__DisplayClass5_0.<<StartAsync>b__1>d.MoveNext() in /Users/nikola/projects/immoledo/GeoIndexer/src/Geoindexer/GeoIndexerConsoleApp.cs:line 41
info: Microsoft.Hosting.Lifetime[0]

UPDATE If works if i don't use insertAsync but just InsertWithInt32Identity

CodePudding user response：

It depends on which SQL Server provider do you use. linq2db supports both available ADO.NET Providers:

For System.Data.SqlClient you have to use dotMorten.Microsoft.SqlServer.Types 1.5,
For Microsoft.Data.SqlClient use dotMorten.Microsoft.SqlServer.Types 2.5