28

I Have a problem. I'm not able to add a Migration to my ASP.NET WebAPI 2 Project. I get error:

"Spatial types and functions are not available for this provider because the assembly 'Microsoft.SqlServer.Types' version 10 or higher could not be found."

I know there are several questions and answers about this, like:

But! The problem is...

  • I already have installed Microsoft.SqlServer.Types.
  • I already have Global.asax configured with: SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin")) into the Application_Start.
  • Reference is set to local copy > true.
  • NuGet packages are all updated.
  • I already try to downgrade and upgrade the package.

This is the full error when I try to run for example Add-Migration v002:

System.InvalidOperationException: Spatial types and functions are not available for this provider because the assembly 'Microsoft.SqlServer.Types' version 10 or higher could not be found.
en System.Data.Entity.SqlServer.SqlTypesAssemblyLoader.GetSqlTypesAssembly() en System.Data.Entity.SqlServer.SqlSpatialServices.GeographyFromText(String wellKnownText) en System.Data.Entity.Spatial.DbGeography.FromText(String wellKnownText) en System.Data.Entity.Migrations.Model.ColumnModel.CreateDefaultValue()
en System.Data.Entity.Migrations.Model.ColumnModel..ctor(PrimitiveTypeKind type, TypeUsage typeUsage) en System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.BuildColumnModel(EdmProperty property, TypeUsage conceptualTypeUsage, TypeUsage defaultStoreTypeUsage, IDictionary2 annotations) en System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.BuildColumnModel(EdmProperty property, ModelMetadata modelMetadata, IDictionary2 annotations)
en System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.<>c__DisplayClass2e3.b__2df(EdmProperty p) en System.Data.Entity.Utilities.IEnumerableExtensions.Each[T](IEnumerable1 ts, Action1 action) en System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.BuildCreateTableOperation(EntitySet entitySet, ModelMetadata modelMetadata) en System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.b__194(EntitySet es) en System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext()
en System.Collections.Generic.List1..ctor(IEnumerable1 collection)
en System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) en System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.Diff(ModelMetadata source, ModelMetadata target, Lazy1 modificationCommandTreeGenerator, MigrationSqlGenerator migrationSqlGenerator, String sourceModelVersion, String targetModelVersion) en System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.Diff(XDocument sourceModel, XDocument targetModel, Lazy1 modificationCommandTreeGenerator, MigrationSqlGenerator migrationSqlGenerator, String sourceModelVersion, String targetModelVersion) en System.Data.Entity.Migrations.DbMigrator.Scaffold(String migrationName, String namespace, Boolean ignoreChanges) en System.Data.Entity.Migrations.Design.MigrationScaffolder.Scaffold(String migrationName, Boolean ignoreChanges) en System.Data.Entity.Migrations.Design.ToolingFacade.ScaffoldRunner.Scaffold(MigrationScaffolder scaffolder) en System.Data.Entity.Migrations.Design.ToolingFacade.ScaffoldRunner.Run() en System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate) en System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
en System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner) en System.Data.Entity.Migrations.Design.ToolingFacade.Scaffold(String migrationName, String language, String rootNamespace, Boolean ignoreChanges) en System.Data.Entity.Migrations.AddMigrationCommand.Execute(String name, Boolean force, Boolean ignoreChanges) en System.Data.Entity.Migrations.AddMigrationCommand.<>c__DisplayClass2.<.ctor>b__0() en System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)

Community
  • 1
  • 1
Martín
  • 3,105
  • 7
  • 25
  • 43
  • Does this answer your question? ['Microsoft.SqlServer.Types' version 10 or higher could not be found on Azure](https://stackoverflow.com/questions/13174197/microsoft-sqlserver-types-version-10-or-higher-could-not-be-found-on-azure) – Jon Schneider Apr 28 '20 at 21:42

4 Answers4

75

After a lot of research I just installed "Microsoft System CLR Types for SQL Server 2012" from:

Worked like a charm!

Martín
  • 3,105
  • 7
  • 25
  • 43
  • 1
    This worked on a new machine in which a project that uses EDMX ( which i hate, if i'm going to use EF, i'm going with code first ) .... It was blowing up and my previous machine for 2 weeks ago had same version of visual studio , 2015 etc.. So other than me installing sql server 2016 for the first time on this new machine... that is the only thing that is different so I'm glad I found your answer, to me since me and several others on same project never had this problem ... it is very frustrating to see this. Thx – Tom Stickel Jul 13 '17 at 23:39
  • Omg, thank you. I have been wrestling with this forever and all the other stuff I found about downloading the nuget for sql types didn't work, I think because my entities are in a separate project/assembly. So, when I tried to do any migration stuff it failed. After installing the CLR MSI everything worked fine. – jmichas Aug 30 '17 at 16:24
  • Which machine did you install these on? (WWW server? DB server? Your local development PC?) – Jon Schneider Apr 28 '20 at 16:16
17

Make sure you are not missing a binding redirect

  <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="10.0.0.0-11.0.0.0" newVersion="14.0.0.0" />
    </dependentAssembly>
Lord Darth Vader
  • 1,895
  • 1
  • 17
  • 26
4

This worked for me.

Add this to the web.config > system.webServer >> runtime >> assemblyBinding node

  <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>
David
  • 3,488
  • 2
  • 21
  • 21
  • Thanks...I installed Install-Package Microsoft.SqlServer.Types, but that alone didn't work. I added that line and it fixed the error. – N8ALL3N May 03 '19 at 15:23
2

the problem may be "virtual". You need to have SqlServerSpatial140.dll in bin folder at the moment you issue add-migration command. Just copy the .dll to bin for development purpose and replace manually the version (x86/x64) during deployment. Sql Server (2012+) already has the assembly installed.

Alex Kudryashev
  • 9,120
  • 3
  • 27
  • 36
  • The dll has been always there. That's not the problem. The issue has been resolved with the accepted green answer. – Martín Apr 23 '17 at 03:51
  • The problem arises when you use MVC Code First. This is not THE solution, just a workaround. – Alex Kudryashev Apr 23 '17 at 04:04
  • The Green Answer installs the .dll to a Sql Server. In 2016+ it is already installed. The problem arises in Code First development only. I don't pretend for a Final Solution, just share my own experience. – Alex Kudryashev Apr 23 '17 at 04:09
  • I have 2016+ already insalled and I had to install the CLR Types anyway – Martín Apr 23 '17 at 04:12
  • Additional installation wouldn't hurt. Thank you for sharing your experience. I'm still in the process. – Alex Kudryashev Apr 23 '17 at 04:16