.net - Entity Framework 6 set connection string runtime

Question

We are in a mixed environment where our application is using both ADO.NET and Entity Framework.
Since both are pointing to the same physical SQL server, we would like to remove the Entity Framework connection string from the config file and then auto build the string based on the current ADO.NET connection strings.
This saves us from mistakes where a developer changed the ADO.NET string but forgot to change the Entity Framework connection string.

I have read this but they did not answer the question.
How do I create connection string programmatically to MS SQL in Entity Framework 6?

If I create my own DbConnection and pass that to the DbContext(existingConnection, contextOwnsConnection) then it throws an error "The context is being used in Code First mode with code that was generated from an EDMX file for either Database First or Model First development."

I am not using Code First.

https://msdn.microsoft.com/en-us/data/jj680699
This talked about code base configuration in EF 6 but the article does not show any code that actually changed the connection string.

UPDATED: More information to help clarify my question.
I am NOT using code first and would like to build a connection string outside of the config file.
The DbContext I am using is a partial class to the auto generated DbContext file that the T4 template is generating.
I was under the impression that I needed to create an inherited DbConfiguration class and do something in that class but the only example I find is using Azure.
https://msdn.microsoft.com/en-us/data/jj680699
There is an article on Code Project that talks about setting the connection string at runtime but the article is based on building a connection string every time I create a new Entity container.
http://www.codeproject.com/Tips/234677/Set-the-connection-string-for-Entity-Framework-at

I would like to be able to use my partial DbContext class to create the connection string so that the caller does not have to do anything special.

UPDATED: Working code for RunTime but not DesignTime
Using code posted by @Circular Reference "listed below", I was able to change the connection string without changing the calls to my Entity class BUT this does not work for DesignTime EDMX file.

public partial class TestEntities : DbContext
{
    public TestEntities() : base(GetSqlConnection(), true)
    {
    }

    public static DbConnection GetSqlConnection()
    {
        // Initialize the EntityConnectionStringBuilder. 
        EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();

        var connectionSettings = ConfigurationManager.ConnectionStrings("Current_ADO_Connection_In_Config");

        // Set the provider name. 
        entityBuilder.Provider = connectionSettings.ProviderName;

        // Set the provider-specific connection string. 
        entityBuilder.ProviderConnectionString = connectionSettings.ConnectionString;

        // Set the Metadata location. 
        entityBuilder.Metadata = "res://*/Models.TestModel.csdl|res://*/Models.TestModel.ssdl|res://*/Models.TestModel.msl";

        return new EntityConnection(entityBuilder.ToString());
    }
}

Now if I can just get the DesignTime working then that would be good.

Answer 1

You can work at design time using the connection string in your config file.

<add name="DWContext" connectionString="metadata=res://*/Database.DWH.DWModel.csdl|res://*/Database.DWH.DWModel.ssdl|res://*/Database.DWH.DWModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=SQLSERVER_INSTANCE;initial catalog=DB_NAME;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

So don't remove it because you need it ONLY at design time.

Work instead in a dynamic way at runtime using this approach (similar to your last one):

Extend the data context partial class:

public partial class DWContext
{
    public DWContext(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {
    }

    /// <summary>
    /// Create a new EF6 dynamic data context using the specified provider connection string.
    /// </summary>
    /// <param name="providerConnectionString">Provider connection string to use. Usually a standart ADO.NET connection string.</param>
    /// <returns></returns>
    public static DWContext Create(string providerConnectionString)
    {
        var entityBuilder = new EntityConnectionStringBuilder();

        // use your ADO.NET connection string
        entityBuilder.ProviderConnectionString = providerConnectionString;

        entityBuilder.Provider = "System.Data.SqlClient";

        // Set the Metadata location.
        entityBuilder.Metadata = @"res://*/Database.DWH.DWModel.csdl|res://*/Database.DWH.DWModel.ssdl|res://*/Database.DWH.DWModel.msl";

        return new DWContext(entityBuilder.ConnectionString);
    }

}

And from your code create a new EF data context with:

private DWContext db = DWContext.Create(providerConnectionString);

Ciao ;-)