Entity Framework 6 SQL Server provider based on Microsoft.Data.SqlClient

This Entity Framework 6 provider is a replacement provider for the built-in SQL Server provider.

This provider depends on the modern Microsoft.Data.SqlClient ADO.NET provider, see my blog post here for why that can be desirable.

The latest build of this package is available from NuGet

Configuration

There are various ways to configure Entity Framework to use this provider.

You can register the provider in code using an attribute:

    [DbConfigurationType(typeof(MicrosoftSqlDbConfiguration))]
    public class SchoolContext : DbContext
    {
        public SchoolContext() : base()
        {
        }

        public DbSet<Student> Students { get; set; }
    }

If you have multiple classes inheriting from DbContext in your solution, add the DbConfigurationType attribute to all of them.

Or you can use the SetConfiguration method before any data access calls:

 DbConfiguration.SetConfiguration(new MicrosoftSqlDbConfiguration());

Or you can add the following lines to your existing DbConfiguration class:

SetProviderFactory(MicrosoftSqlProviderServices.ProviderInvariantName, Microsoft.Data.SqlClient.SqlClientFactory.Instance);
SetProviderServices(MicrosoftSqlProviderServices.ProviderInvariantName, MicrosoftSqlProviderServices.Instance);
// Optional
SetExecutionStrategy(MicrosoftSqlProviderServices.ProviderInvariantName, () => new MicrosoftSqlAzureExecutionStrategy());

You can also use XML/App.Config based configuration:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
	<configSections>
		<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
	</configSections>
	<entityFramework>
		<providers>		
			<provider invariantName="Microsoft.Data.SqlClient" type="System.Data.Entity.SqlServer.MicrosoftSqlProviderServices, ErikEJ.EntityFramework.SqlServer" />
		</providers>
	</entityFramework>
</configuration>

If you use App.Config with a .NET Core / .NET 5 or later app, you must register the DbProviderFactory in code once:

DbProviderFactories.RegisterFactory(MicrosoftSqlProviderServices.ProviderInvariantName, Microsoft.Data.SqlClient.SqlClientFactory.Instance);

If you use an EDMX file, update the Provider name:

<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <edmx:Runtime>
    <edmx:StorageModels>
      <Schema Namespace="ChinookModel.Store" Provider="Microsoft.Data.SqlClient" >

and also update the provider name inside the EntityConnection connection string like this:

 <add 
    name="Database" 
    connectionString="metadata=res://*/EFModels.csdl|res://*/EFModels.ssdl|res://*/EFModels.msl;provider=Microsoft.Data.SqlClient;provider connection string=&quot;data source=server;initial catalog=mydb;integrated security=True;persist security info=True;" 
    providerName="System.Data.EntityClient" 
 />

Code changes

In order to use the provider in an existing solution, a few code changes are required (as needed).

using System.Data.SqlClient; => using Microsoft.Data.SqlClient;

The following classes have been renamed to avoid conflicts with classes in the existing SQL Server provider:

SqlAzureExecutionStrategy => MicrosoftSqlAzureExecutionStrategy

SqlConnectionFactory => MicrosoftSqlConnectionFactory

SqlDbConfiguration => MicrosoftSqlDbConfiguration

SqlFunctions => MicrosoftSqlFunctions

SqlHierarchyIdFunctions => MicrosoftSqlHierarchyIdFunctions

SqlProviderServices => MicrosoftSqlProviderServices

SqlFunctions => MicrosoftSqlFunctions

SqlServerMigrationSqlGenerator => MicrosoftSqlServerMigrationSqlGenerator

SqlSpatialFunctions => MicrosoftSqlSpatialFunctions

SqlSpatialServices => MicrosoftSqlSpatialServices

Known issues

If an older version of EntityFramework.dll is installed in the .NET Framework GAC (Global Assembly Cache), you might get this error:

The 'PrimitiveTypeKind' attribute is invalid - The value 'HierarchyId' is invalid according to its datatype

Solution is to remove the .dll from the GAC, see this for more info

Feedback

Please report any issues, questions and suggestions here