Change Database during runtime in Entity Framework, without changing the Connection c# entity-framework sql-server


I want to start using Entity Framework in our upcoming release and I have a server that hosts 50 databases with identical schemas.

For each of those databases, I don't require a new connection. For data management and speed (this is a WebAPI application), I don't want to create a new EF context every time I communicate to one of the 50 databases if I don't have to. Unless, of course, if this occurs each time a request is sent to the server, in which case there is no great concern.

The only thing I actually require is the capacity to modify the USE [databasename] instruction, which, I presume, finally makes its way from EF to the server.

Is it possible to complete this in code? Does EF keep track of a read/write property in the context for the database name, which might be modified instantly before executing SaveChanges(), etc.?

Thanks a lot!


9/17/2013 4:09:29 AM

Accepted Answer

You may like to review:

  • Regarding sending an existing SQL Connection to the EntityFramework Context, Why query
  • Also describing how to modify the database on an existing connection at this piece.

If you require any extra assistance, kindly let me know.

Link 2 has been updated to refer to the SqlConnection.ChangeDatabase approach.
Thus, code would eventually like the following:

MetadataWorkspace workspace = new MetadataWorkspace(
  new string[] { "res://*/" }, 
  new Assembly[] { Assembly.GetExecutingAssembly() });

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
using (EntityConnection entityConnection = new EntityConnection(workspace, sqlConnection))
using (NorthwindEntities context = new NorthwindEntities(entityConnection))
  // do whatever on default database
  foreach (var product in context.Products)

  // switch database
  Console.WriteLine("Database: {0}", connection.Database);
5/23/2017 10:31:22 AM

Popular Answer

Work smart, not hard, please!!!

MYContext localhostContext = new MYContext();
MYContext LiveContext = new MYContext();
//If your databases in different servers
LiveContext.Database.Connection.ConnectionString = LiveContext.Database.Connection.ConnectionString.Replace("localhost", "Live");
//If your databases have different Names
LiveContext.Database.Connection.ConnectionString = LiveContext.Database.Connection.ConnectionString.Replace("DBName-Localhost", "DBName-Live");

databases should all have the same structure.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow