c# - Connection Strings for Entity Framework

Question

I want to share same Database information across multiple entities in Silverlight.. but I want the connection string to be named xyz and have everyone access that connection string from machine.config...

The meta data part of the entities will be different since I didn't name the entities the same..

Can I put multiple entities in that metadata section?

Here is an example.. I want to use this connection string but note that i put multiple entities in the metadata section..

Basically I want to take this Connection String

<add name="XYZ" connectionString="metadata=res://*/ModEntity.csdl|res://*/ModEntity.ssdl|res://*/ModEntity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SomeServer;Initial Catalog=SomeCatalog;Persist Security Info=True;User ID=Entity;Password=SomePassword;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

And this Connection String

 <add name="XYZ" connectionString="metadata=res://*/Entity.csdl|res://*/Entity.ssdl|res://*/Entity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SOMESERVER;Initial Catalog=SOMECATALOG;Persist Security Info=True;User ID=Entity;Password=Entity;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

To make this Connection String

<add name="XYZ" connectionString="metadata=res://*/Entity.csdl|res://*/Entity.ssdl|res://*/Entity.msl|res://*/ModEntity.csdl|res://*/ModEntity.ssdl|res://*/ModEntity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SOMESERVER;Initial Catalog=SOMECATALOG;Persist Security Info=True;User ID=Entity;Password=SOMEPASSWORD;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

But it simply doesn't work. Neither project can connect to it.

string encConnection = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
Type contextType = typeof(test_Entities);
object objContext = Activator.CreateInstance(contextType, encConnection);
return objContext as test_Entities; 

Answer 1

Unfortunately, combining multiple entity contexts into a single named connection isn't possible. If you want to use named connection strings from a .config file to define your Entity Framework connections, they will each have to have a different name. By convention, that name is typically the name of the context:

<add name="ModEntity" connectionString="metadata=res://*/ModEntity.csdl|res://*/ModEntity.ssdl|res://*/ModEntity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SomeServer;Initial Catalog=SomeCatalog;Persist Security Info=True;User ID=Entity;Password=SomePassword;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
<add name="Entity" connectionString="metadata=res://*/Entity.csdl|res://*/Entity.ssdl|res://*/Entity.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=SOMESERVER;Initial Catalog=SOMECATALOG;Persist Security Info=True;User ID=Entity;Password=Entity;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

However, if you end up with namespace conflicts, you can use any name you want and simply pass the correct name to the context when it is generated:

var context = new Entity("EntityV2");

Obviously, this strategy works best if you are using either a factory or dependency injection to produce your contexts.

Another option would be to produce each context's entire connection string programmatically, and then pass the whole string in to the constructor (not just the name).

// Get "Data Source=SomeServer..."
var innerConnectionString = GetInnerConnectionStringFromMachinConfig();
// Build the Entity Framework connection string.
var connectionString = CreateEntityConnectionString("Entity", innerConnectionString);
var context = new EntityContext(connectionString);

How about something like this:

Type contextType = typeof(test_Entities);
string innerConnectionString = ConfigurationManager.ConnectionStrings["Inner"].ConnectionString;
string entConnection = 
    string.Format(
        "metadata=res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl;provider=System.Data.SqlClient;provider connection string="{1}"",
        contextType.Name,
        innerConnectionString);
object objContext = Activator.CreateInstance(contextType, entConnection);
return objContext as test_Entities; 

... with the following in your machine.config:

<add name="Inner" connectionString="Data Source=SomeServer;Initial Catalog=SomeCatalog;Persist Security Info=True;User ID=Entity;Password=SomePassword;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

This way, you can use a single connection string for every context in every project on the machine.