In an attempt to “keep up with the times,” I added Entity Framework 6 (EF) to a project I’m working on. I found out a couple of interesting tidbits, like using a stored procedure for upserts is much faster than coding it in .NET/EF and modifying an entry/row without a primary key in EF can get complex (and difficult). One of the hurdles that my lead brought up was the fact that we can play with EF on dev all we want, but when we push to QA/stage/production–especially to production–we don’t have the connection string and it can get tricky instructing IT on how to properly edit it.

Luckily, there are massive amounts of information out on the web for programmatically creating an EF connection string, but they’re mostly all outdated. Or were code-first. Or have some weird nuance that wasn’t relevant. I’m sure if I took the time to read instead of skimming for the silver bullet, I would have found this solution earlier.

My EF model is database-first. When I created the model, I pointed it to an existing DB using a ADO.NET connection string in web.config. EF generated the diagram, classes based on the tables I chose to bring in, methods based on the stored procedures I chose to bring in, the EF connection string, and all the other moving parts like magic. Great.

Now I needed to figure out how to use a different EF connection string.

Copious amounts of searching led me to this sample chapter which covers generating an EF connection string using EntityConnectionStringBuilder (see ex. 16.2). This particular class lives in the System.Data.Entity.Core.EntityClient namespace in EF6, but in System.Data.EntityClient in EF5. Unfortunately, the top MSDN entry from a Google search will point you to EF5’s namespace, which doesn’t exist in EF6.

The next tricky bit, to continue using the example from the sample chapter, is you can’t just pull your ADO.NET connection string from web.config and send it as a parameter into the constructor of EntityConnectionStringBuilder. An ADO.NET connection string is not the same as an EF connection string, which is what EntityConnectionStringBuilder expects (this specificity is not very clear from the docs, either).

An illustration. Suppose I have the following ADO.NET connection string in my web.config:

<add name="SampleDB" connectionString="Data Source=localhost;Initial Catalog=Sample;
    Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

To construct my EF connection string, I will want to do the following:

...
using System.Data.Entity.Core.EntityClient
...
var entityBuilder = new EntityConnectionStringBuilder();
entityBuilder.Metadata = @"res://*/Models.SampleModel.csdl|res://*/Models.SampleModel.ssdl|res://*/Models.SampleModel.msl";
entityBuilder.ProviderConnectionString = ConfigurationManager.ConnectionStrings["SampleDB"].ConnectionString;
entityBuilder.Provider = ConfigurationManager.ConnectionStrings["SampleDB"].ProviderName
string entityConn = entityBuilder.ToString();

That is, I create an EntityConnectionStringBuilder without any parameters, then set the three important parts: metadata, provider connection string (or the ADO.NET connection string), and the provider (e.g. “System.Data.SqlClient”). The metadata is a hardcoded value from what I can tell. I just copied it from the existing EF connection string.

Now that I have a proper EF connection string, I need to send it to my entity context. This bit isn’t too difficult, but it makes you wonder why EF doesn’t already have it. What you basically want to do is add an additional entity context constructor that takes a single string parameter. Identify the partial class and its constructor, then create another codefile (e.g., SampleEntities.cs) with the same class signature but with a slightly different constructor. This is to avoid your “hack” from getting deleted when your EF model regenerates its codefiles. Below is an example:

public partial class SampleEntities
{
	public SampleEntities(string conn) : base (conn)
	{
	}
}

Simple, right? …I’m sure this solution will become outdated when EF7 is released.