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.

Solution 1

You are getting the Code First mode exception because you are passing a DbConnection built with the ADO.NET connection string. This connection string does not include references to metadata files, so EntityFramework does not know where to find them.

To create a DbContext with an appropriate programmatically set connection string, use the EntityConnectionStringBuilder class.

var entityBuilder = new EntityConnectionStringBuilder();

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

// Set the Metadata location.
entityBuilder.Metadata = @"res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl";
var dbContext = new DbContext(entityBuilder.ConnectionString);

Solution 2

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 ;-)

Solution 3

Other way is to declare another connection string in the config file and use it with the following alternative constructor:

class TestEntities : DbConnect {
 
    public TestEntities (string connectionName)
       : base($"name={connectionName}")
    {
    }

...

Then to use this solution just:

1. add another connection to the config file:

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

2. call it like this:

   var db = new TestEntities (connectionName:"other");
   

Comments

[AaronLS]

I think you would be using ObjectContext instead of DbContext, since you are not using Code First. I am not 100% certain on this relationship. This hints at this: msdn.microsoft.com/en-us/library/bb738461(v=vs.110).aspx and is linked from here describing creating a connection: msdn.microsoft.com/en-us/library/…

[AaronLS]

Connection strings for CodeFirst usually are standard connection strings, where as connection strings for non-CodeFirst are usually references to csdl/ssdl/msl files.

[Suncat2000]

You can use an ObjectContext, but it's a lot of trouble if you don't need to dig down into it for special properties. As of EF 5, its normal to use DbContext. The EntityConnection is a superset of a regular connection string, with additional properties to access the conceptual and storage sections of the EDMX file.

[goroth]

Your answer works but I would have to do this every time I create an Entity container in code. I need some way of doing this inside the partial DbContext calls... but even then, I'm not sure that will work for the designer. I think I have to do something with DbConfiguration but I can't find any good examples.

[Nick Patsaris]

Check this. You will have to write a bit more code and while it works, it's not good from a maintainability point of view. It's probably better to keep EF connection string in the config file.

[Nils Guillermin]

This doesn't work for me, I get the "This context is being used in Code First mode..." message.

[jacktric]

@NilsGuillermin try to delete and create the data model again and be sure to choose the "Create from existing database (Database first)" option.

[Nils Guillermin]

@jacktricI don't have that option, are either "Code First from database" and "EF Designer from database" what I want?

[Ian Ippolito]

I would be fine with this implementation if I could get to work. However, I am getting an error of "keyword not supported 'metadata'" when attempting to access the DB context database created by the above.

[Ian Ippolito]

@jacktric, I like the elegance of this architecture/solution. However, when I tried using it, the compiler could not compile the constructor. public DWContext(string nameOrConnectionString) : base(nameOrConnectionString) The error was that the base class does not accept any parameters.

[jacktric]

@IanIppolito Your data context (DWContext in the example) is a partial class that ihnerits from DbContext. And DbContext has a constructor that take a string as documented here msdn.microsoft.com/en-us//library/gg679467(v=vs.113).aspx

[zagZzig]

nice solution. It is worthy pointing out that, when extending the data context partial class, that piece of code (as shown above) should be put into a separated file as the original file is auto generated by Visual Studio. That is why keyword partial is there.