Entity Framework cannot update database

The answers above didn't work for me, so I did a bit more digging and am sharing my findings here.

Summary: There was a change in the Microsoft SQL Server System CLR Types (SQLSysCLRTypes.msi) library between SQL Server 2012 SP2 (11.0.2100.60) and SP3 (11.0.6020.0) and this problem can be fixed by upgrading this package and any stray DLLs to the latest version (corresponding to 2012 SP4 / 11.0.7001.0 at time of writing).

There are really only two things in this package:

  • Microsoft.SqlServer.Types.dll - the .NET wrapper library
  • SqlServerSpatial110.dll - the native library containing the spatial functionality

Note that myriad versions of SQLSysCLRTypes.msi are available, corresponding with every major/minor release of SQL Server, but annoyingly they are all published with the same filename and unless you perform a full SQL Server installation then they tend to be manual prerequisites for installing things from the SQL Server Feature Pack (for example, see https://www.microsoft.com/en-us/download/details.aspx?id=56041)

From the SQL 2012 SP3 version of the package onwards, SqlServerSpatial110.dll exports the function SetClrFeatureSwitchMap, which is called from somewhere within the .NET wrapper DLL. Prior to SP3, that function didn't seem to exist and the .NET wrapper didn't try to use it. (you can list DLL exports using dumpbin /exports <dll file>)

If the CLR Types MSI package is installed on a particular machine, and a different minor version of those DLLs is in your .NET program's working directory, then you can get the error. This could easily happen if you distribute your program with its dependency libraries to avoid extra installation steps for the end-user.

Whenever .NET libraries are installed to the system and included in the Global Assembly Cache (GAC), the system version will always be loaded by a .NET program even if a "local" copy can be found in the working directory. For native libraries, the working directory copy is used first. This means that when you reference Microsoft.SqlServer.Types in your application and have both DLLs of matching versions in your application directory, if Microsoft.SqlServer.Types is installed on the system with the same major version (ie. 11.0.0.0), then it can have problems when it tries to load its native library dependencies and gets an older version of SqlServerSpatial110.dll from the working directory instead of the correct version from wherever it may be installed on the system.

How to Fix: Make sure any copies of SqlServerSpatial110.dll have the same minor version as any copies of Microsoft.SqlServer.Types.dll, and make sure you have the latest version of each. This probably only applies to SQL Server 2012 but it is possible that similar problems could occur in newer versions of SQL Server with eventual Service Pack releases.

Note that setting "Specific Version" to "True" for references to Microsoft.SqlServer.Types (in Visual Studio) doesn't have an effect, since all the SQL Server 2012 CLR Types library versions expose the same version number to .NET (11.0.0.0), regardless of which service pack they are from.

References:

  • How can I force .NET to use a local copy of an assembly that's in the GAC
  • https://docs.microsoft.com/en-us/cpp/build/reference/dumpbin-reference

I know I am late to the party, but I had the same problem with MSSQL 2012 and it was really annoying. I was not able to run any query in tables with Spacial column(s). It was a bit tricky. I summarize my approach, just in case for others,

The reason is because of some inconsistency between SP3 and CLR. The best way to that is checking C:\Windows\assembly and if you see some Microsoft.SqlServer.Types here you need to remove and install them again. Well removing them is a bit tricky:

  1. Possibly uninstall all SQL server updates from windows update, I just did this for Service packs 1,2,3

  2. Go to this location in registery HKLM\SOFTWARE\Classes\Installer\Assemblies\Global here

  3. Delete all keys with the name Microsoft.SqlServer.Types, but before that take a registry backup Be careful not to mess up the registry.

  4. Run Developer Command Prompt as Administrator and run this command gacutil -i Microsoft.SqlServer.Types

  5. Repair the original version of SQL server you already have.

Finally, I was able to execute any query on any table even with Geometry (Spatial) data.

Hope this helps some people.


I had the same issue, and fixed it by adding the following lines to the web.config of my application:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" />
      <bindingRedirect oldVersion="1.0.0.0-11.0.0.0" newVersion="10.0.0.0" />
    </dependentAssembly>
  </assemblyBinding>
</runtime>

This forces the EntityFramework to use the version 10 of the SqlServer.Types.dll, which doesn't have the Geometry type apparently.


So, If I add the following line of code to the start-up of the application it will use the SQL 2014 version of the Microsoft.SqlServer.Types assembly which doesn't seem to have the problem stated above.

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

This is fine for machines that have SQL Server 2014 SDK installed.

I have also submitted a bug with Microsoft here:

https://connect.microsoft.com/SQLServer/Feedback/Details/2139143

Tags:

C#

Entity Framework

Recent Posts