Search code examples
c#sql-serverentity-frameworkcode-firstclass-library

Class Library, Entity Framework Code First & Microsoft.SqlServer.Types

I am having a few issues trying to get code-first Entity Framework 6.0 to build a migration with Microsoft.SQLServer.Types. I still keep getting:

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

Everywhere I look they keep talking about running: 

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

or similar in the global.asax. I need to run this in a class library which handles the data layer of my application, so there isn't a startup event.

I have put in 

[assembly: PreApplicationStartMethod(typeof(Test.Startup), "Start")]
namespace Test
{
    public class Startup
    {
        public static void Start()
        { 
             SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);
        }
    }
}

But that has not had any effect. Anyone got any other ideas?

Thanks

Chris

Solution

  • Ooh! I just had this problem last week!

    Let's start by clarifying a few things. This error is caused by the lack of having an assembly to manage the Sql Server Spatial types. You can solve this issue by either:

    • Installing SQL Server on the machine
    • Taking a dependency on a NuGet package.

    Typically, going with the NuGet package is easier, so I'm going to explain that.

    The version of the NuGet package that you get depends on the version of Sql Server that you have running. My version of Sql Server is 11.0.6020.0, so I installed the 11.0.2 version of the NuGet package. This is super important to match the versions.

    There are three assemblies that you'll need.

    1. Microsoft.SqlServer.Types.dll
    2. msvcr<Version>.dll
    3. SqlServerSpatial<Version>.dll

    Microsoft.SqlServer.Types.dll is a managed assembly, while the other two are native assemblies.

    If you install the correct version of the NuGet package, then all of these should be handled for you.

    After you install the NuGet package, it'll open a readme, and it gives the example on how to load the native assemblies by using the Loader class.

    Code sample:

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

    Lastly, you'll need to override the SqlServerTypesAssemblyName property in the SqlProviderServices static class. Note that this is only available in a later version of EntityFramework. I'm using EntityFramework 6.1.3. In my research, others were saying that this wasn't available in earlier versions.

    Once again, I was using version 11.0.2 to match my Sql Server version, so I used the following code:

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

    If you're using a newer version of Sql Server, you should be able to replace the 11 with 12, 13, or 14 without issues.

    If it's not working, here's a quick checklist:

    • Ensure that Microsoft.SqlServer.Types.dll is referenced.
    • Ensure that the native assemblies are copied to your bin directory under the ..\SqlServerTypes\<Platform>\ folder. There should be two.
    • Ensure that you're setting calling the LoadNativeAssemblies and setting the SqlServerTypesAssemblyName before any database operations, including creating your DbContext instance.
    • If your DbContext is in a separate assembly than the executing assembly, you may need to reference the Microsoft.SqlServer.Types assembly. It may not be copied over via the build/reference process.