How to move asp.net entity framework connectionStrings to within appSettings section of web.config

Question

I am in the process of deploying an ASP.NET WebAPI project to Elastic Beanstalk in AWS.

The WebAPI project relies on Entity Framework, and has a connection string property defined in the web.config under <connectionStrings> as follows:

<connectionStrings>
   <add name="myDBEntities" connectionString="metadata=res://*/myDBModel.csdl|res://*/myDBModel.ssdl|res://*/myDBModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=myServer\test;initial catalog=myDB;user id=myDBUser;password=PASS==;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

What I am aiming to do is have the entity framework "myDBEntities" connectionString name resolve via an entry under <appSettings> section of the web.config instead, like so:

  <appSettings>
    <add key="myDBEntities" value="metadata=res://*/myDBModel.csdl|res://*/myDBModel.ssdl|res://*/myDBModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=myServer\test;initial catalog=myDB;user id=myDBUser;password=PASS==; multipleactiveresultsets=True;App=EntityFramework&quot; providerName=System.Data.EntityClient;" />
  </appSettings>

Note: I have dodgily added "providerName=System.Data.EntityClient;" to the end of the myDBEntities appSetting value in an attempt to get it to work / resolve. But no luck.

The removing of the connectionString setting and moving to under appSettings is so that it will configurable as an environment configuration setting in Elastic Beanstalk.

Any ideas / suggestions?

Can you specify exactly what is the purpose to do so!! There may be an easier solution! — Mukesh Modhvadiya, Sep 21 '15 at 05:50
When .NET attempts to resolve a connectionString named "myDBEntities", I want it to be able to resolve using an "appSetting" key/value pair instead. — DanH, Sep 21 '15 at 06:37
That you already mentioned in question, what is the purpose of that! — Mukesh Modhvadiya, Sep 21 '15 at 06:39
The removing of the **connectionString** setting and moving to under **appSettings** is so that it will configurable as an environment configuration setting in Elastic Beanstalk. Are you aware of AWS Elastic Beanstalk? — DanH, Sep 21 '15 at 06:42
Yes but never worked on it. So I don't know how your connection string will be selected. — Mukesh Modhvadiya, Sep 21 '15 at 06:54
Normally I would just use connectionstring, but I know exactly why you want to do this as I'm working on several projects that are doing appsettings with environment configuration so for me this makes sense. — Tom Stickel, Oct 01 '15 at 23:13
The one thought I had about doing this is with web.config and app.config transformations instead though... — Tom Stickel, Oct 01 '15 at 23:13
Yes I had considered that also, but I did not want to change the build artifact between environments. This way I can use the one application zip file between all my environments (Dev -> UAT -> Prod etc). As all config settings are now stored within AWS. The only web.config transformation I use is to remove "appSettings" element when publishing to AWS. — DanH, Oct 02 '15 at 09:44

score 2 · Answer 1 · edited May 23 '17 at 11:53

Found a viable solution, what this solution proposes is dynamically creating the connection strings when the application starts.

Firstly create an appSetting with a key name of the connection string you wish to replace in this case "myDBEntities". For its value use the same metadata connection string resulting in:

<appSettings>
    <add key="myDBEntities" value="metadata=res://*/myDBModel.csdl|res://*/myDBModel.ssdl|res://*/myDBModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=myServer\test;initial catalog=myDB;user id=myDBUser;password=PASS==;multipleactiveresultsets=True;App=EntityFramework&quot;" />
</appSettings>

(Note: do not worry about the provider name in the appSetting string for now this will be addressed in the following section)

Add the following code to your global.asax file:

    protected void Application_Start()
    {
        // ... other stuff in here if not already empty.

        var fieldInfo = typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);
        if (fieldInfo != null)
        {
            fieldInfo.SetValue(ConfigurationManager.ConnectionStrings, false);

            // Check for AppSetting and Create
            if (ConfigurationManager.AppSettings["myDBEntities"] != null)
            {
                ConnectionStringSettings myDB = new ConnectionStringSettings("myDBEntities", ConfigurationManager.AppSettings["myDBEntities"]);
                myDB.ProviderName = "System.Data.EntityClient";
                ConfigurationManager.ConnectionStrings.Add(myDB);
            }
        }
    }

Important:

When creating your "ConnectionStringSettings" the name needs to match that of that entity framework connectionString you are replacing (ie. "myDBEntities" ).
Be sure to specify the correct provider Name (i.e "System.Data.EntityClient") This can be taken from your original connectionString setting.
Be sure to comment out or remove your original connectionString setting.

Entity framework is now able to resolve appSetting "myDBEntities" as a Entity Framework connection.

The following article was helpful in getting to this solution Can I Add ConnectionStrings to the ConnectionStringCollection at Runtime?

Hope this helps others getting their .NET apps into the AWS Elastic Beanstalk cloud.

Did you ever find a better solution for transforming the connection strings in elastic beanstalk? — Kevin R., Dec 27 '16 at 18:50
In Elastic Beanstalk the appSettings within a web.config become environment variables in AWS EC2. The best way to formalise this would be to include them into a Cloudformation script. — DanH, Dec 28 '16 at 08:29

How to move asp.net entity framework connectionStrings to within appSettings section of web.config

1 Answers1