Azure-functions-core-tools: Support specifying Connection Strings in appsettings.json

[lindydonna]

Some customers uses connection strings for specifying SQL connections etc. Since we don't yet support connection strings, this means you'd have to write different code for it to work locally.

To do:

• [x] Update fetch app settings to grab connection strings
• [x] Inject connection strings into ConfigurationManager

Could potentially do something like the following:

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

[markmonster]

I tried to get this working but I face the issue that providerName is required as well.

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

Any idea how I would be able to tackle this?

[ahmelsayed]

Yes I'm not setting the providerName to anything. It seems that Azure sets it to System.Data.SqlClient with no way of changing it. Would that be good enough?

btw, are you using EntityFramework to load that connection string? One thing you can try is going to the path where func.exe is on your machine and editing func.exe.config to add the providerName yourself to see if it works with System.Data.SqlClient.

If we want to support specifying a providerName in appsettings.json then we'll need to update the schema of the file from

{
  "ConnectionStrings": {
    "ConnectionStringName": "ConnectionStringValue"
  }
}

to something like

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

I was trying to avoid that complexity but I'm not an expert on EF. These flags are sometimes magic to me :)

[markmonster]

I think to support the standard SQL Server and Azure SQL setting it to System.Data.SqlClient makes sense. To be honest, I've never set it to something else that System.Data.SqlClient, but thats probably based on the environment that I've been in. Using Entity Framework in combination with Azure SQL or SQL Server. In the past when I used different database technologies like Oracle or PostgreSQL I used it in combination with NHibernate or plain ADO.NET.

I would like to follow the Azure implementation. Giving more flexibility locally can result in "It runs on my machine, but it doesn't in the cloud"-syndrome.

[petermauger]

I'm running into issues with this at the moment. I have a DB-first EntityFramework model that I need to use in my Function project. I'm using the identical EF Connection String that I'm using in my API project (all working fine) but when the function is triggered I get a logged exception stating that providerName is not provided.

I set up the Function 'server' on the Portal but have done all the development in VS2017.3 (then connected to the portal created 'server' using the Publish wizard). I can't see the appSettings.json file anywhere to try the syntax mentioned in this issue (and #193) so am hoping someone can explain how I can get this working?

[Adrian10988]

I've tried the approaches mentioned here to fix the provider name issue but am not having luck. It seems the AppConfig.cs class is not pulling in the provider name. This class is located in System.Data.Entity.Internal.AppConfig. I can't dig any further as I am unable to get this decompile software to work correctly. More info here: https://stackoverflow.com/questions/46698103/missing-providername-in-connection-string-local-settings-json-when-using-entity

[Escoto]

@ahmelsayed I have a function app project that runs in local with EF, but after deploying I've been getting the same issue of "providerName not provided" any idea?
I tried this Missing ProviderName when debugging AzureFunction as well as deploying azure function but with no luck

[Adrian10988]

@Escoto I just replied to your other comment without seeing this. That SO answer should work for you. Could you post your code please?

[petermauger]

I've got a hack-around for this that's working for me. It's a little long winded but give it a shot. For reference LiveEntities is my EF context type name:

First find your .edmx file in the solution and expand it then open the .Context.tt subfile.
You should see something like:

public <#=code.Escape(container)#>()
    : base("name=<#=container.Name#>")
{

<#
if (!loader.IsLazyLoadingEnabled(container))
{

>

    this.Configuration.LazyLoadingEnabled = false;

<#
}
foreach (var entitySet in container.BaseEntitySets.OfType())
{
// Note: the DbSet members are defined below such that the getter and
// setter always have the same accessibility as the DbSet definition
if (Accessibility.ForReadOnlyProperty(entitySet) != "public")
{

>

    <#=codeStringGenerator.DbSetInitializer(entitySet)#>

<#
}
}

>

}

That creates the parameterless constructor for your context when you update the model. Take a copy of that and paste it immediately below then in the pasted code replace:

public <#=code.Escape(container)#>()
    : base("name=<#=container.Name#>")

with

    public <#=code.Escape(container)#>(string connStr, string metadata)
    : base(((EntityConnectionStringBuilder)new EntityConnectionStringBuilder()
        {
            Provider = "System.Data.SqlClient",
            ProviderConnectionString = connStr,
            Metadata = metadata
        }).ToString())

This creates a second constructor that takes your DB connection string as pieces and puts together an EntityConnectionString that will work.

Once you've saved the .Context.tt file open the .edmx designer, right-click and select Update Model From Database (or whatever you normally do to get the code generation to trigger). The code-behind for your model should now have the new constructor.

Now to instantiate your EF context use:

using (LiveEntities liveEntities = new LiveEntities(Environment.GetEnvironmentVariable("DatabaseConnectionString", EnvironmentVariableTarget.Process), "res:///Live.LiveEntities.csdl|res:///Live.LiveEntities.ssdl|res://*/Live.LiveEntities.msl"))
{
// EF action here
}

To configure you need to do two things. The first is hunt down your EF generated connection string (which you'd LOVE to just use in your Function like you do in your Web Apps but can't) and take the initial EF specific metadata (everything from metadata= to ;provider= ie "res:///Live.LiveEntities.csdl|res:///Live.LiveEntities.ssdl|res://*/Live.LiveEntities.msl") and use it in your code. This part shouldn't change so it can be hard coded.

The second part is to take the database connection string and add it to your Function Application Settings (Azure Portal -> Your function app -> Application Settings) by adding a string value with a valid key (I used DatabaseConnectionString) and everything from the EF generated connection string after 'provider connection string=' not including the "s.

NOTE: your database connection string won't be as secure as it would be if it was hidden in the Function Application Settings -> Connection Strings part of the config. I'd love to hear from anyone if there is some way to secure that part...

[huricen]

I also ran into this ugly issue. Has this been resolved? I used the work around by @petermauger but instead of hacking the template generator, I created a partial class that had the custom constructor:

public partial class YourEntities
{
    public YourEntities(string connStr, string metadata)
          :  base(
            new EntityConnectionStringBuilder()
            {
                Provider = "System.Data.SqlClient",
                ProviderConnectionString = connStr,
                Metadata = metadata
            }.ToString()) { }
 }

I also added a static NewInstance() function which wraps the custom constructor so new instances can be created without having to specify what should be static configuration:

    public static YourEntitiesNewInstance()
    {
        return new YourEntities(
            Environment.GetEnvironmentVariable("ConnectionString"),
            "<YourEntities Metadata>");
    }