Using entity framework on multiple databases

Question

I am writing a payroll system that will integrate with a pre-existing system. The original system had a master database that handled user management and some global configuration, below that there are multiple databases each identical in structure, basically each database is one companies payroll database, all these are tied to the main database because it belongs to a parent company who has many subsidiaries each with their own HR department.

What I was wondering is if there is any way that I can, based on either a cookie or another method that stores what company they wish to connect to, dynamically change the entity frameworks target database based on their input using a filter?

Here's an example:

User A logs in to the site, page loads with available companies that the user has permission to access, user will then select a company, they have admin privileges in that company, they add an employee, before that action is run, asp.net will switch the connection string to the appropriate database then add the record.

Answer 1

It is very simple. I have:

public WMSEntities() : base("name=WMSEntities") //WMSEntities is conection string name in     web.config also the name of Entitiframework { } 

already in autogenerated Model.Context.cs of edmx folder

To connect to multiple database in runtime, I created another constructor that takes connection string as parameter like below in same file Model.Context.cs

public WMSEntities(string connStringName)     : base("name=" + connStringName) { } 

Now I added other connection string in Web.Config for example

<add name="WMSEntities31" connectionString="data source=TESTDBSERVER_NAME;initial catalog=TESTDB;userid=TestUser;password=TestUserPW/>  <add name="WMSEntities" connectionString="data source=TESTDBSERVER_NAME12;initial catalog=TESTDB12;userid=TestUser12;password=TestUserPW12/> 

Then, when connecting to database I call below method passing connetionString name as parameter

public static List<v_POVendor> GetPOVendorList(string connectionStringName) {     using (WMSEntities db = new WMSEntities(connectionStringName))     {                        vendorList = db.v_POVendor.ToList();                      } } 

Answer 2

EF6 has better support for multiple DB access from Same context. Here is a snippet from EF5. Managing the database initializer setting prior is important. You may not want to trigger ANY migrations. i.e, use this before

Database.SetInitializer(new ContextInitializerNone<MyDbContext>());

but to answer the question: Yes you can

var conn = GetSqlConn4DbName(dataSource,dbName ); var ctx = new MyDbContext(conn,true);    public DbConnection GetSqlConn4DbName(string dataSource, string dbName) {         var sqlConnStringBuilder = new SqlConnectionStringBuilder();         sqlConnStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? DefaultDataSource : dataSource;         sqlConnStringBuilder.IntegratedSecurity = true;         sqlConnStringBuilder.MultipleActiveResultSets = true;          var sqlConnFact = new SqlConnectionFactory(sqlConnStringBuilder.ConnectionString);         var sqlConn = sqlConnFact.CreateConnection(dbName);         return sqlConn;     }    public class ContextInitializerNone<TContext> : IDatabaseInitializer<TContext> where TContext : DbContext {     public void InitializeDatabase(TContext context) {  } } 

Also see StackOverflow answer using migration, sample code, and dynamic db connection