Can't cast SqlGeography when withdrawing data from DB

Question

I can't get SqlGeography object from MS SQL DB, I use .net core app and linq2db as the provider, but I have an exception: "Can't create 'GIS_CH_DB.sys.geography' type or '' specific type for Geometry."

But I'm use

var provider = (SqlServerDataProvider)DataConnection.GetRegisteredProviders()[LinqToDB.ProviderName.SqlServer2014];
            provider.AddUdtType<SqlGeography>("Geography", null, LinqToDB.DataType.Udt);
            provider.AddUdtType<SqlGeometry>("Geometry", null, LinqToDB.DataType.Udt);

which should cast SQLGeography to correct object. Does somebody know how to solve this issue?

First of all, why do you call AddUdtType for spatial types? linq2db already register them automatically or if it is not happening, you can register it manually using `SqlServerTools.ResolveSqlTypes(assembly)`. If it will not help, post some examples, that give you those errors. — , Jul 13 '19 at 07:13
@DLuk, thanks for the advice but unfortunately it didn't help. An issue that I have columns with type `SqlGeometry` in GIS Db but in .net Core `SqlTypes` doesn't support. So I used unofficial SqlType libs. So issue that when I want to get value `SqlGeometry` or `SqlGeography` from DB table, appear exception: "Can't create 'GIS_CH_DB.sys.geography' type or '' specific type for Geometry." — Artem Danchenko, Jul 15 '19 at 10:15
Which package you use? We test linq2db with this one https://github.com/dotMorten/Microsoft.SqlServer.Types and it works in our tests. So wether you use something else of we need to add more tests. — , Jul 16 '19 at 07:12
Probably you shold track this issue https://github.com/linq2db/linq2db/issues/1836 for now — , Aug 02 '19 at 08:11
So as you can see from linked issue - it is probably due to missing assembly version redirects. SqlClient uses hardcoded references to pretty old version of types assembly — , Aug 02 '19 at 11:50
@DLuk Hello, sorry for the late answer. I use also github.com/dotMorten/Microsoft.SqlServer.Types. I tried `SqlServerTools.ResolveSqlTypes(System.Reflection.Assembly.GetAssembly(typeof(SqlGeometry)))` in Startup file but it didn't help. How can I change the reference for SqlClient? — Artem Danchenko, Aug 05 '19 at 15:41
ok, I was able to reproduce it. Will report soon how to fix it — , Aug 06 '19 at 09:22

score 6 · Accepted Answer · 2019-08-06T10:33:54.180

Edit: added retrown instead of return null

As it was mentioned in comments, this is due to version conflicts between spatial types assembly (Microsoft.SqlServer.Types), requested by sql client and actual assembly you have. While for .net framework application you can handle it using binding redirects, for .net core you cannot do it because:

no binding redirects in .net core
even if there will be any - they will not work due to different public keys

You need to register custom assembly resolver to fix it (based on https://github.com/dotnet/core/issues/290#issuecomment-280451104):

// somewhere in your init code
AssemblyLoadContext.Default.Resolving += OnAssemblyResolve;

Assembly OnAssemblyResolve(AssemblyLoadContext assemblyLoadContext, AssemblyName assemblyName)
{
  try
  {
    AssemblyLoadContext.Default.Resolving -= OnAssemblyResolve;
    return assemblyLoadContext.LoadFromAssemblyName(assemblyName);
  }
  catch
  {
    if (assemblyName.Name == "Microsoft.SqlServer.Types")
      return typeof(SqlGeography).Assembly;
    throw;
  }
  finally
  {
    AssemblyLoadContext.Default.Resolving += OnAssemblyResolve;
  }
}

I will update linq2db documentation to mention it and also check if we can improve this situation on our side

Can't cast SqlGeography when withdrawing data from DB

1 Answers1