How to use a dynamic connection string using a Model First approach but still use the data model in the EDMX?

Question

I have created an EDMX using EF 5 with the Model First approach, i.e. I started with a blank designer and modeled my entities. Now I want to be able to use this model defined in the EDMX but supply runtime SQL Server connection strings without modyfing the config file.

I know how to pass a connection string to the DbContext but the issue is locating the metadata for the mappings within the assembly.

For example, my EDMX has this connection string in the app.config

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

The part that I am missing is the "metadata=res://*/Data.DataContext.EntityFramework.MesSystem.csdl|res://*/Data.DataContext.EntityFramework.MesSystem.ssdl|res://*/Data.DataContext.EntityFramework.MesSystem.msl;"

I want to be able to create a DbContext programmatically passing in a SQL Server connection string but "add on" the metadata portion.

This is what I would like to be generated by the T4 file...

public partial class MesSystemEntities : DbContext
{
    public MesSystemEntities()
        : base("name=MesSystemEntities")
    {
    }

    public MesSystemEntities(string sqlServerConnectionString)
        : base(GetEfConnectionString(sqlServerConnectionString))
    {    
    }

    private string GetEfConnectionString(string sqlServerConnectionString)
    {
       // values added by T4 generation
       string format = "metadata=res://*/Data.DataContext.EntityFramework.MesSystem.csdl|res://*/Data.DataContext.EntityFramework.MesSystem.ssdl|res://*/Data.DataContext.EntityFramework.MesSystem.msl;;provider=System.Data.SqlClient;provider connection string=\"{0}\"";    
       return String.Format(format, sqlServerConnectionString);
    }
...
}

My question is how can I get the metadata I need in the T4 generation file to create the Entity Framework connection without hardcoding it for each EDMX file

OR

is there an easier way to load the metadata from the assembly programmatically?

@BobHorn - Because we already have a set of standard connection strings to our SQL Server databases. The EDMX used here will part of a framework consumed by other developers in different applications and thus have their own app.config files. It would be easier not to have to have these developers remember to add that nasty long EF specific connection string to their apps — Jim, Dec 19 '12 at 18:45
@BobHorn - In addition, the metadata part is embedded in our framework assembly and won't change, so why agaian, force the consumer of this framework assembly to enter all that extra resource/metadata connection string hooey... — Jim, Dec 19 '12 at 18:53
Have you looked at EntityConnectionStringBuilder class yet? http://msdn.microsoft.com/en-us/library/system.data.entityclient.entityconnectionstringbuilder.metadata.aspx — Pawel, Dec 19 '12 at 18:58

score 8 · Accepted Answer · answered Dec 19 '12 at 18:58

I had the same issue, so instead of relying on all the meta data in the connection string (which I think is not a good idea) I wrote a method to create it from a standard connection string. (I should probably refactor it into an Extension method for DbContext, but this should do)

internal static class ContextConnectionStringBuilder
{
  // Modified Version of http://stackoverflow.com/a/2294308/209259
  public static string GetEntityConnectionString(string ConnectionString, 
                                                 Type ContextType)
  {
    string result = string.Empty;

    string prefix = ContextType.Namespace
      .Replace(ContextType.Assembly.GetName().Name, "");

    if (prefix.Length > 0
        && prefix.StartsWith("."))
    {
      prefix = prefix.Substring(1, prefix.Length - 1);
    }

    if (prefix.Length > 1
        && !prefix.EndsWith("."))
    {
      prefix += ".";
    }


    EntityConnectionStringBuilder csBuilder = 
      new EntityConnectionStringBuilder();

    csBuilder.Provider = "System.Data.SqlClient";
    csBuilder.ProviderConnectionString = ConnectionString.ToString();
    csBuilder.Metadata = string.Format("res://{0}/{1}.csdl|"
                                        + "res://{0}/{1}.ssdl|"
                                        + "res://{0}/{1}.msl"
                                        , ContextType.Assembly.FullName
                                        , prefix + ContextType.Name);

    result =  csBuilder.ToString();

    return result;
  }
}

Basic usage is something like:

string connString = 
  ConfigurationMananager.ConnectionStrings["name"].ConnectionString;

string dbConnectionString = ContextConnectionStringBuilder(connString,
                                                           typeof(MyDbContext));

var dbContext = new MyDbContext(dbConnectionString);

This was the approach I was thinking of but you saved me from revinventing it. — Jim, Dec 19 '12 at 19:02
Oh yeah, it took a while to get the `Res:` right, it was a good 2 hour test making sure the string was Exactly right. It also allows the Context to live in another DLL, which is good because my DbContexts are application agnostic. — Erik Philips, Dec 19 '12 at 19:04
So if this can be done with one extension method, why do Microsoft seem to discourage it by throwing an exception and telling you to work from a code-first approach instead? Seems like there may be a reason not to do it... — Jez, Nov 18 '15 at 09:13
Because this isn't for the code first approach, it's for [tag:database-first] approach... — Erik Philips, Nov 18 '15 at 16:40
I'm in the same situation but i can't reassign the dbContext variable to :base(name=myname) in my Entity Class derived from DBContext. Where i'm getting wrong ? Any help ? — Franco, May 31 '23 at 11:10

How to use a dynamic connection string using a Model First approach but still use the data model in the EDMX?

1 Answers1

Linked