Using entity framework on multiple databases
.net, entity-framework
Solution
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
Problem
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.