EF6.5 + Azure + ASP.NET MVC + Microsoft.Data.SqlClient ProviderName

[3nth]

Locally our MVC NET Framework app is able to use Microsoft.Data.SqlClient just fine having made all the relevent changes, and with the connection string in Web.Config setting the ProviderName="Microsoft.Data.SqlClient" appropriately.

However, in Azure Web Services, the connection strings don't have ProviderName and I cannot figure out how to get the app to use Microsoft.Data.SqlClient instead of System.Data.SqlClient.

[ErikEJ]

yeah, sadly Azure App Service configuration system is hard coded to "System.Data.SqlClient" as you discovered.

But I think there is a workaround as described here: ErikEJ#129 (comment)

@3nth

[3nth]

Heh, didn't think to tell it a little lie: "I got your System.Data.SqlClient right here"

We are using this in a common library, which is also used by other apps which are upgraded to .NET Core with no issue:

    public class EbisuConfiguration : MicrosoftSqlDbConfiguration
    {
        public EbisuConfiguration()
        {
            SetProviderFactory(MicrosoftSqlProviderServices.ProviderInvariantName, Microsoft.Data.SqlClient.SqlClientFactory.Instance);
            SetProviderServices(MicrosoftSqlProviderServices.ProviderInvariantName, MicrosoftSqlProviderServices.Instance);
            SetExecutionStrategy(MicrosoftSqlProviderServices.ProviderInvariantName, () => new MicrosoftSqlAzureExecutionStrategy());
        }
    }
    
    [DbConfigurationType(typeof(EbisuConfiguration))]
    public class EbisuContext : DbContext
    {}

and was trying this in Application_Start to no avail

DbConfiguration.SetConfiguration(new EbisuConfiguration());

Will try having this NET Framework web app use it's own.

Is the fact that configuration referenced by the DbContext via DbConfigurationType attribute is different going to be an issue?

Maybe this is possible? (Not sure how important it was for the Configuration to be in the same assembly)

    public class EbisuConfiguration : MicrosoftSqlDbConfiguration
    {
        public EbisuConfiguration()
        {
            SetProviderFactory(MicrosoftSqlProviderServices.ProviderInvariantName, Microsoft.Data.SqlClient.SqlClientFactory.Instance);
            SetProviderServices(MicrosoftSqlProviderServices.ProviderInvariantName, MicrosoftSqlProviderServices.Instance);
            SetExecutionStrategy(MicrosoftSqlProviderServices.ProviderInvariantName, () => new MicrosoftSqlAzureExecutionStrategy());
            
            // tell me lies, tell me sweet little lies
            SetProviderFactory("System.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance);
            SetProviderServices("System.Data.SqlClient", MicrosoftSqlProviderServices.Instance);
            SetExecutionStrategy("System.Data.SqlClient", () => new MicrosoftSqlAzureExecutionStrategy());
        }
    }

[3nth]

To answer my own question, this appears to work just fine.

Locally, you can test the "Azure Connection Strings Experience" by setting ProviderName="System.Data.SqlClient" on the connection strings, and will get Microsoft.Data.SqlClient at runtime with this (same assembly as Global.asax.cs not required)

    public class EbisuConfiguration : MicrosoftSqlDbConfiguration
    {
        public EbisuConfiguration()
        {
            // Map "Microsoft.Data.SqlClient" -> Microsoft.Data.SqlClient Provider
            SetProviderFactory(MicrosoftSqlProviderServices.ProviderInvariantName, Microsoft.Data.SqlClient.SqlClientFactory.Instance);
            SetProviderServices(MicrosoftSqlProviderServices.ProviderInvariantName, MicrosoftSqlProviderServices.Instance);
            SetExecutionStrategy(MicrosoftSqlProviderServices.ProviderInvariantName, () => new MicrosoftSqlAzureExecutionStrategy());

            // Map "System.Data.SqlClient" -> Microsoft.Data.SqlClient Provider
            SetProviderFactory(SqlProviderServices.ProviderInvariantName, Microsoft.Data.SqlClient.SqlClientFactory.Instance);
            SetProviderServices(SqlProviderServices.ProviderInvariantName, MicrosoftSqlProviderServices.Instance);
            SetExecutionStrategy(SqlProviderServices.ProviderInvariantName, () => new MicrosoftSqlAzureExecutionStrategy());
        }

[ErikEJ]

My eyes hurt, but if that works for you. Wonder if order matters?

[3nth]

I'm thinking order does matter for the .NET Core apps which I believe come in with no ProviderName set in the connection string, but since these now map to the same providers it doesn't actually matter?

Also, since we've done this on our DbContext:

    [DbConfigurationType(typeof(EbisuConfiguration))]
    public class EbisuContext : DbContext

We don't actually need to do this in the Global.asax.cs Application_Start

DbConfiguration.SetConfiguration(new EbisuConfiguration());

[ErikEJ]

@AndriySvyryd Feel free to close this!