Solving ‘Spatial types and functions are not available’ with Entity Framework

Mon, 27 Mar 2017 16:38:33 +0000

Using SQL Server’s Geospatial features with Entity Framework is awesome. I was crusing along just fine with using DbGeography for an ASP.Net application I’m working on… Right up until I deployed to the server. Yep. It worked on my machine, but alas, I was hitting a runtime exception on my test server.

This was the exception:

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

Luckily, I ran across a Stack Overflow answer that almost helped me get all the way there in solving this exception. It was actually the second-most up-voted answer that helped me the most. You’re welcome to reference these if you want, but since the info is scattered between the question and the two answers, I’m assembling it here for your convenience.

Fixing ‘Spatial types and functions are not available'

1 – Install the Microsoft.SqlServer.Types package from NuGet

You can install it using the NuGet Package Manager UI, or from the command line:

> Install-Package Microsoft.SqlServer.Types

2 – Follow readme.htm instructions

After you install the NuGet package, a readme.htm file is opened and displayed to you. If you don’t see it for some reason, a new SqlServerTypes folder was added to your project. You can expand it and double-click readme.htm to open it.

Follow the instructions within.

Depending on the type of app you’re deploying, you need to perform different steps. I happened to be deploying an ASP.Net MVC app, so I followed the instructions for adding a line of code to Global.asax.cs. Your situation may be different, but the Readme instructions are clear on what to do.

ASP.Net Web Applications

Open Global.asax.cs and add the following to the list of registrations in Application_Start():

1protected void Application_Start()
2{
3    // Enables use of spatial data types
4    SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));
5
6    // Other registrations...
7}

Asp.Net Websites

Open Default.aspx.cs and add

 1public partial class _Default : System.Web.UI.Page
 2{
 3    static bool _isSqlTypesLoaded = false;
 4
 5    public _Default()
 6    {
 7        if (!_isSqlTypesLoaded)
 8        {
 9            SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~"));
10            _isSqlTypesLoaded = true;
11        }
12        
13    }
14}

Desktop Applications

Add the following before any spatial operations are performed.

1SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

3 – Tell Entity Framework which version of the assembly to use

Most of the explanations of how to solve the “‘Microsoft.SqlServer.Types’ version 10 or higher” error stopped at step 2.

After performing the steps that readme.htm outlined, I re-deployed the app, and things were still broken. Same exception. What gives??

This is where that second-most-popular answer came into play. It gave me the clue I needed.

Back in Global.asax.cs (or wherever you performed the steps for #2 above), add the following”

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

Note: Depending on which version of the NuGet package you installed, you need to adjust the Version number from 14.0.0.0 to the correct version, as appropriate. I installed the latest, which at the time is version 14.x, so that’s what I put in.

??? The SqlServerTypesAssemblyName property doesn’t exist! ???

In my case, I ran into another small hurdle. When I tried to set the SqlServerTypesAssemblyName property, it didn’t exist!

Upon further inspection, I discovered that I only had Entity Framework version 6.1.**** installed. I updated to 6.1.3, and the property lit up. I set it appropriately, redeployed the app, and it magically worked.

Here’s hoping that this helps bring together several pieces of information to get you going with using SQL Server Geospatial data types with Entity Framework!

Spatial on Andrew Bancroft