Entity Framework to multiple databases (same schema) at runtime?

Question

First of all, let me state I'm very new to EF. With that said, here's my dilemma:

There will be an ASP.NET App migrated to ASP.NET MVC. I would like to utilize EF for this. There is one main database which stores "client information". Apart from that, every "client" has their own database. These are the constraints we have.

Currently, client information in the main DB that enables me to build a connection string per client and make individual SQL calls.

How would I accomplish the same thing in Entity Framework? Each database WILL have the same schema. Is there a way to programmatically switch the Connection String? These DBs are currently on the same server, but that's not a requirement and it may be a completely different server.

Any ideas?

Multiple connection strings in the Web.config would be a last resort. Even then, I'm not sure how exactly to wire this up.

Thank you in advance.

Answer 1

When you build a data context, here's how to programmatically change the connection string at runtime by modifying the Context.Connection property:

//Get the connection string from app.config and assign it to sqlconnection string builder
SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(((EntityConnection)context.Connection).StoreConnection.ConnectionString);
sb.IntegratedSecurity = false;
sb.UserID ="User1";
sb.Password = "Password1";

//set the object context connection string back from string builder. This will assign modified connection string.
((EntityConnection)context.Connection).StoreConnection.ConnectionString = sb.ConnectionString;

Taken from: http://sivapinnaka.spaces.live.com/blog/cns!B027EF7E7070AD69!211.entry

Answer 2

If you work through an EntityConnection in the constructor of your entities object, you can change the database pretty easily.

EntityConnection con = new EntityConnection(connString);
con.ChangeDatabase(dbName);
using (Entities context = new Entities(con))
{
    // Some code here
}