Azure-functions-core-tools: support adding providerName in ConnectionStrings (local.settings.json)

[aarondcoleman]

Hi All. I'm picking up on a design decision made back here: https://github.com/Azure/azure-functions-cli/issues/46

I think hard-coding the providerName to System.Data.SqlClient is a mistake. We use Entity Framework in Model-First mode and in order to use the out-of-box EF data context we either need the ConnectionStrings to have a providerName left unspecified or have it set to System.Data.EntityClient.

I'd agree with @mkamonster's intent to mirror Azure to prevent "works on local". However, to really mirror the functionality in azure, then the option to just turn providerName off needs to be added at least for us, but as show below, there are other options too. We use the Custom option as illustrated in the screenshot below:

From EF documentation https://msdn.microsoft.com/en-us/library/jj556606(v=vs.113).aspx:

Designer based models use special EF connection strings. For example:

<connectionStrings> 
  <add name="BlogContext"  
        connectionString="metadata=res://*/BloggingModel.csdl| 
                                                       res://*/BloggingModel.ssdl| 
                                                       res://*/BloggingModel.msl; 
                                           provider=System.Data.SqlClient 
                                           provider connection string= 
                                               &quot;data source=(localdb)\v11.0; 
                                               initial catalog=Blogging; 
                                               integrated security=True; 
                                               multipleactiveresultsets=True;&quot;" 
     providerName="System.Data.EntityClient" /> 
</connectionStrings>

Lastly, I'd advocate that this not be just binary because

1. it looks like the portal now does support a lot more types:
   

1. It's common for an application that uses App.config to specify any number of connection string and then perhaps only transform a few in portal and thereby being able to use any providerName. Local testing should allow this as well.

2. Seeing that (2) is possible, many customers (us included) do transforms in deploy steps through VSTS, so again, local testing should allow us to have all the options we through other means.

Thanks for consideration. I realize this is a breaking change but I'd support @ahmelsayed's suggestion of:

{
  "ConnectionStrings": {
    "ConnectionStringName": {
      "ConnectionString": "Value",
      "providerName": "provider"
  }
}

as this will make our migration from WebJobs to Functions much easier and not require lots of hacks into the Text Transforms of Entity Framework's default DbContext generation.

[lindydonna]

@aarondcoleman Thanks for the detailed info--you obviously are more of an expert on connection string settings. :). The tooling is still in preview, so there's still time to make the change. We'll triage this next week.

[ahmelsayed]

It doesn't need to be a breaking change. the value for "ConnectionStringName" could just be a string | { ConnectionString: string; ProviderName: string }

that way we don't need to introduce a breaking change.

[aarondcoleman]

I'm cool with that!

[lindydonna]

@ahmelsayed Is this an easy change? If so, I'll add it to the next milestone.

[ahmelsayed]

yes, it's a simple change

[Adrian10988]

Is there a way to set this provider name in the portal settings?

[Escoto]

This works in local, but how can we make it work in Azure? @lindydonna @ahmelsayed

[Adrian10988]

@Escoto There are a number of things you can do.

if you have the luxury of being able to supply the connection string to the DBContext constructor then do that. If you have to use the parameterless constructor of your DBContext then refer to my answer on SO: https://stackoverflow.com/questions/46698103/missing-providername-when-debugging-azurefunction-as-well-as-deploying-azure-fun

Look at the second part to the accepted answer

[Escoto]

@Adrian10988 thanks, that's exactly what I am doing, and, I think it worked, but then this error showed up Exception while executing function: AlertCheckRulesFunction -> Keyword not supported: 'metadata'.
So, apparently the fix on the T4 works, but once in azure EF don't like the entire connection string.

[jawandsingh]

@Escoto On Azure Portal, AppSettings you need to change ConnectionString Type to Custom instead of using SQLAzure.