Entity Framework cannot update database

Question

My application crashes with the following error whenever I save to the DB.

Unable to find an entry point named 'SetClrFeatureSwitchMap' in DLL 'SqlServerSpatial110.dll'.

This error started yesterday afternoon after a Windows Update and PC Reboot. The dll is not referenced in the project and is not in the bin folder.

I use EF5 and I can connect to the DB and pull data but when I call ObjectContext.SaveChanges() the error occurs.

The application does not use geometry, so I have no idea where this is coming from.

Answer 1

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.

Answer 2

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://learn.microsoft.com/en-us/cpp/build/reference/dumpbin-reference

Answer 3

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

Answer 4

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.

Answer 5

From my experience with this error, this happens only with Entity Framework 5 targeting .NET 4 (as opposed to .NET 4.5) when using a SQL Server with SQL Server Service Pack 3. More info on possible root cause here. Solutions that worked for me:
1) KdBoer's fix, or
2) Update the application to Entity Framework 6

Answer 6

It happened to me when using Visual Studio 2022 instead of previous 2019, although I think it came because in VS 2022 the option to “Use the 64 bit version of IIS Express for web sites and projects” (inside Options/Projects and Solutions/Web Projects) was check by default and in 2019

Probably caused by other installation in between but the solution (or workaround) for me was to uninstall "Microsoft SQL Server System CLR Types for SQL Server 2012" both 32 and 64 bit versions. It happens that I have many more of them installed so I had no trouble until now. Before trying this I provided all of the options here (changing web.config etc) to no avail.

The solution provided by @javacow was the source of the idea because it says:

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.

I tried to match the minor and major version running the repair option in the install program of both "Microsoft SQL Server System CLR Types for SQL Server 2012" but it didn't run because it was missing some setup packages, so I decided to uninstall (in order to install them later, which seems I can't because it complains there are newer versions installed). So I tried to run and my program worked without issues even without this version.