c# - Missing ProviderName when debugging AzureFunction as well as deploying azure function

Question

I have an issue getting a DbContext to correctly pull my connection string from my local.settings.json

Context:

• This is an Azure function project
• The main problem code is in System.Data.Entity.Internal.AppConfig
• Although I have a local.settings.json file this is not dotnet core. It's .net 4.6.1

Error message:

'The connection string 'ShipBob_DevEntities' in the application's configuration file does not contain the required providerName attribute."'

Json configuration:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "AzureWebJobsDashboard": ""
},

"ConnectionStrings": {
"ShipBob_DevEntities": {
  "ConnectionString": "metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='data source=***;initial catalog=***;persist security info=True;User Id=***;Password=***;;multipleactiveresultsets=True;application name=EntityFramework'",
  "providerName": "System.Data.EntityClient"
    }
  }
}  

Configuration versions tested:

• Moving the provider name into the actual ConnectionString token value : same error ocurrs
• Setting the provider attribute inside the ConnectionString attribute to EntityClient: this did nothing

• Making ShipBob_DevEntities a string value = to the value of ConnectionString : this throws new errors the likes of which are

  keyword metadata is not supported

• I tried using an ADO connection string which throws a code first exception which seems to occur when your connection string is incorrect in a database first approach.

I've taken the liberty to decompile EntityFramework.dll using dotPeek and have traced the problem down to System.Data.Entity.Internal.LazyInternalConnection.TryInitializeFromAppConfig. Inside this method there is a call to LazyInternalConnection.FindConnectionInConfig which spits out a ConnectionStringSettings object that has it's ProviderName value set to null. Unfortunately I am unable to debug the AppConfig.cs class which it seems to use to generate this value so I am stuck.

So far I have consulted these two articles. One of which states to put the provider name as it's own token; however, this is not working.

https://github.com/Azure/azure-functions-cli/issues/193
https://github.com/Azure/azure-functions-cli/issues/46

Does anyone know the correct format to use in local.settings.json for an Entity Framework connection?

Answer 1

I went through several similar questions and answers here. Many of them are either misleading or assuming everybody is on the same level and understands how the azure functions are working. there is no answer for newbies like me. I would like to summarize here my solution step by step. I dont think that provided answer is the best option because it forces you to change the auto generated edmx files which can be overwritten by mistake or next update of your edmx from database. Also best option here is to use Connection strings instead of App settings in my opinion.

1. most important thing is that we understand local.settings.json file IS NOT FOR AZURE. it is to run your app in the local as the name is clearly saying. So solution is nothing to do with this file.

2. App.Config or Web.Config doesnt work for Azure function connection strings. If you have Database Layer Library you cant overwrite connection string using any of these as you would do in Asp.Net applications.

3. In order to work with, you need to define your connection string on the azure portal under the Application Settings in your Azure function. There is Connection strings. there you should copy your connection string of your DBContext. if it is edmx, it will look like as below. There is Connection type, I use it SQlAzure but I tested with Custom(somebody claimed only works with custom) works with both.

metadata=res:///Models.myDB.csdl|res:///Models.myDB.ssdl|res://*/Models.myDB.msl;provider=System.Data.SqlClient;provider connection string='data source=[yourdbURL];initial catalog=myDB;persist security info=True;user id=xxxx;password=xxx;MultipleActiveResultSets=True;App=EntityFramework

4. After you set this up, You need to read the url in your application and provide the DBContext. DbContext implements a constructor with connection string parameter. By default constructor is without any parameter but you can extend this. if you are using POCO class, you can amend DbContext class simply. If you use Database generated Edmx classes like me, you dont want to touch the auto generated edmx class instead of you want to create partial class in the same namespace and extend this class as below.

This is auto generated DbContext

namespace myApp.Data.Models
{   

    public partial class myDBEntities : DbContext
    {
        public myDBEntities()
           : base("name=myDBEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

}

this is the new partial class, you create

namespace myApp.Data.Models
{
    [DbConfigurationType(typeof(myDBContextConfig))]
    partial class myDBEntities
    {

        public myDBEntities(string connectionString) : base(connectionString)
        {
        }
    }

      public  class myDBContextConfig : DbConfiguration
        {
            public myDBContextConfig()
            {
                SetProviderServices("System.Data.EntityClient", 
                SqlProviderServices.Instance);
                SetDefaultConnectionFactory(new SqlConnectionFactory());
            }
        }
    }

5. After all you can get the connection string from azure settings, in your Azure Function project with the code below and provide to your DbContext myDBEntities is the name you gave in the azure portal for your connection string.

var connString = ConfigurationManager.ConnectionStrings["myDBEntities"].ConnectionString;


 using (var dbContext = new myDBEntities(connString))
{
        //TODO:
}