Unable to load SqlServerSpatial.dll after referencing Microsoft.SqlServer.Types package

Question

I'm running into an issue when trying to access some spatial data from a Sql Server table.

The exception I receive is

Unable to load DLL 'SqlServerSpatial.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

I tried the solutions mentioned here, and here, which is in essence

1. Install the Microsoft.SqlServer.Types package.
2. Ensure that Microsoft.SqlServer.Types, EntityFramework and EntityFramework.SqlServer references are set to Copy always
3. Load the native libraries (in my case, adding SqlServerTypes.Utilities.LoadNativeAssemblies(HttpRuntime.BinDirectory); to the end of the Application_Start method in Global.asax.cs

After doing these, I end up with the same exception occurring in the same place, which in a way makes sense, because I don't have a SqlServerSpatial.dll, but rather a SqlServerSpatial140.dll included with the Microsoft.SqlServer.Types package.

What can I do to resolve this issue?

Answer 1

I came across this same problem recently and decided to dig into it because all of the answers I found on Stack Overflow were voodoo and hand-waving to fix the symptom instead of understand the problem.

Note: This applies to Entity Framework 6 and .NET Framework. Since newer versions of .NET don't have a GAC this may not be a problem there.

The problem

What I found is that when using EntityFramework.SqlServer alongside Microsoft.SqlServer.Types can lead to runtime assembly version problems depending on what versions of the Types assembly you have installed in the GAC or other locations in the assembly resolution path. This is pretty easy to happen, since this assembly comes with SQL Server, SQL Server Management Studio, as a standalone installer (which, ironically, some answers suggest using to fix this problem), and probably other ways.

When using SQL Server CLR types like DbGeography, Entity Framework delegates the work out to the Microsoft.SqlServer.Types library (even though there isn't a dependency between the packages). To do this it has to load that assembly, and to decide which assembly version to load, it uses the System.Data.Entity.SqlServer.SqlTypesAssemblyLoader class that has the following heuristic:

1. If System.Data.Entity.SqlServer.SqlProviderServices.SqlServerTypesAssemblyName is populated, use that.
2. Try to load version 11.
3. Try to load version 10.
4. Try to load each version from 20 down to 12 until one works.

The current major version of Microsoft.SqlServer.Types is 14. This means if you have an older version of 11 or 10 installed in the GAC, those versions will be preferred by Entity Framework and will be loaded into your AppDomain, even if you already have version 14 loaded from the nuget package.

This leads to multiple versions of the Microsoft.SqlServer.Types assembly being loaded with the older version of the library getting used in ways that are not supported, resulting in runtime exceptions. Symptoms include looking for the old SqlServerSpatial.dll native library instead of the expected SqlServerSpatial140.dll native library which comes with version 14 of the Microsoft.SqlServer.Types nuget package (and is loaded via the Loader.cs file that comes with that package).

You can verify this is happening by enabling Fusion binding logs, or by adding the following to your program (before and after using Entity Framework) and noting that you go from one assembly to two with different versions.

AppDomain.CurrentDomain.GetAssemblies()
         .Where(a => a.FullName.Contains("Microsoft.SqlServer.Types")).ToArray();

The end result is an exception being raised, such as Exception has been thrown by the target of an invocation. --> Unable to load DLL 'SqlServerSpatial.dll'.

The solution

There are two separate ways to address the problem, depending on your situation and preference. You need to do one of these (I suggest leaving a comment for the poor future developer who runs into this again after Nuget packages are updated):

1. Add an assembly redirect binding for Microsoft.SqlServer.Types so that Entity Framework (and everything else) is forced to use the version you have via Nuget:

   <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>
   

2. Specify the version you want Entity Framework to use via the static property SqlProviderServices.SqlServerTypesAssemblyName property. You'll need to do this early in the lifecycle of the program, such as in Startup / Main / Application_Start / etc.

   System.Data.Entity.SqlServer.SqlProviderServices.SqlServerTypesAssemblyName 
       = "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
   

That's it.

Answer 2

Make a copy of your SqlServerSpatial140.dll (or download a newer version) and name your copy SqlServerSpatial.dll and move it to your system32 directory. Worked for me.