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?

Answer 1

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