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

Question

I created EDMX using EF 5 with the Model First approach, i.e. I started with an empty designer and modeled my objects. Now I want to be able to use this model defined in EDMX, but supply SQL Server runtime strings without modifying the configuration file.

I know how to pass the connection string to DbContext, but the problem is finding metadata for the mappings within the assembly.

For example, my EDMX has this connection string in 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 I'm missing is "metadata=res://*/Data.DataContext.EntityFramework.MesSystem.csdl|res://*/Data.DataContext.EntityFramework.MesSystem.ssdl|res://*/Data.DataContext.EntityFramework.MesSystem.msl;"

I want to create a DbContext programmatic transfer in the SQL Server connection string, but "add" part of the metadata.

This is what I would like to create using a 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 to get the metadata that I need in the T4 generation file to create an Entity Framework connection without hard coding for each EDMX file

OR

Is there an easier way to programmatically load metadata from an assembly?

Answer 1

I had the same problem, so instead of relying on all the metadata in the connection string (which, I think, is not a good idea), I wrote a method to create it from the standard connection string. (I should probably reorganize it into an extension method for DbContext, but that should do it)

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

The main use is something like:

 string connString = ConfigurationMananager.ConnectionStrings["name"].ConnectionString; string dbConnectionString = ContextConnectionStringBuilder(connString, typeof(MyDbContext)); var dbContext = new MyDbContext(dbConnectionString);