Microsoft.SqlServer.Types assembly causes problems with geography datatype. How to fix?

1

After upgrading and changing my project to target network .NET 4.8 and upgrading used Nuget packages to the latest version in package manager. This brought up a host of problem. Most pressing was the inability to use Geography data type in DataReader and Entity Framework. The error in data reading components was: System.InvalidOperationException: DataReader.GetFieldType(x) returned null. The Entity Framework just swallowed the exception (which is an extremely bright idea in itself) and returned empty dataset.

Because those messages are actually meaningless and contribute nothing to revealing the cause I have created a trivial console application to research the issue.

Simple console app,

target framework .Net 4.8

installed nugget package Microsoft.SqlServer.Type version 14.0.1016.290

using System;
using System.Configuration;
using System.Data.SqlClient;

namespace TestGeo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
            {
                sqlConnection.Open();
                using (SqlCommand sqlCommand = new SqlCommand( "SELECT TOP 1 Location FROM MyTable WHERE Location IS NOT NULL"))
                {
                    sqlCommand.Connection = sqlConnection;
                    //sqlCommand.CommandTimeout = 10;
                    SqlDataReader reader  = sqlCommand.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            var type = reader.GetFieldType(0);    // <- This line causes exception.
                            Console.WriteLine(type.ToString());
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows.");
                    }
                    reader.Close();
                }
            }
        }
    }
}

This brings up the following errors.

Managed Debugging Assistant 'BindingFailure' Message=Managed Debugging Assistant 'BindingFailure' : 'The assembly with display name 'Microsoft.SqlServer.Types' failed to load in the 'Load' binding context of the AppDomain with ID 1. The cause of the failure was: System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)'

System.IO.FileLoadException HResult=0x80131040 Message=Could not load file or assembly 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
Source=mscorlib StackTrace: at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) in f:\dd\ndp\clr\src\BCL\system\reflection\assembly.cs:line 1951

System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object. Source=TestGeo
StackTrace: at TestGeo.Program.Main(String[] args) in C:\Visual Studio\TestGeo\Program.cs:line 24

Seeing that assembly Microsoft.SqlServer.Types Version=11.0.0.0 could not be loaded I tried to use binding redirect in app.config file redirecting any older version to 14.0.0.0.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=myserver.example.com;Initial Catalog=MyDatabase;User Id=MyUser;Password=supersecretpassword;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  <runtime>
    <assemblyBinding>
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
        <bindingRedirect oldVersion="0.0.0.0-14.0.0.0" newVersion="14.0.0.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

Yet, this did not solve the problem. I got

Managed Debugging Assistant 'BindingFailure' Message=Managed Debugging Assistant 'BindingFailure' : 'The assembly with display name 'Microsoft.SqlServer.Types' failed to load in the 'Load' binding context of the AppDomain with ID 1. The cause of the failure was: System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

System.IO.FileLoadException HResult=0x80131040 Message=Could not load file or assembly 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
Source=mscorlib StackTrace: at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) in f:\dd\ndp\clr\src\BCL\system\reflection\assembly.cs:line 1951

System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object. Source=TestGeo
StackTrace: at TestGeo.Program.Main(String[] args) in C:\Visual Studio\TestGeo\Program.cs:line 24

System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object. Source=TestGeo
StackTrace: at TestGeo.Program.Main(String[] args) in C:\Visual Studio\TestGeo\Program.cs:line 24

Why is this. Does assembly reference not work as expected, is it ignored? I managed to get this simple example to work by downgrading to nuget package Microsoft.SqlServer.Types version 11.0.2. But why does it not work with newer versions? I finf it hard to comprehend that one has to use old defunct library? Why?

c#
asp.net
.net
sql-server
.net-assembly
asked on Stack Overflow Dec 26, 2019 by user6694745 • edited Dec 28, 2019 by user6694745

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0