Switch Databases Using EF: Part Deux
October 15, 2013 Leave a comment
Consider you are a company where you want to keep each of your customer’s data in its own database on the same server. Each of the databases has the identical schema. For example:
In the Data Access Layer, you are using Entity Framework and you want to move between databases at run time. I blogged about one possible solution to this problem here, but I was thinking about this some more and I have a different (better?) idea.
The first thing to realize is that EF does not make this easy. There is not a Contxext.ChangeDatabase(String) method and the constructor does not allow to pass in a connection string. This tight-coupling smells of poor design, but fortunately this is not insurmountable.
The first step is to create a Model (NorthwindModel in this case). When you inspect the context, you can see that Microsoft made the entities a partial class
So you can add a class to your project like so:
- public partial class NorthwindEntities
- {
- public NorthwindEntities(String connectionString)
- {
- base.Database.Connection.ConnectionString = connectionString;
- }
- }
Then, in your consuming class, you can create a method like this:
- internal static String CreateConnectionStringWithCustomDatabase(String baseConnectionString, String database)
- {
- SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(baseConnectionString);
- connectionStringBuilder.InitialCatalog = database;
- return connectionStringBuilder.ToString();
- }
And then you can switch databases. Just pass in the default connection string and the new database name like so:
- static void Main(string[] args)
- {
- Console.WriteLine("Start");
- String defaultConnectionString = "data source=DIXON12;initial catalog=TBD;integrated security=True;MultipleActiveResultSets=True";
- String actualConnectionString = CreateConnectionStringWithCustomDatabase(defaultConnectionString, "Northwind");
- using (NorthwindEntities entities = new NorthwindEntities(actualConnectionString))
- {
- foreach (var product in entities.Products)
- {
- Console.WriteLine(product.ProductName);
- }
- }
- Console.WriteLine("-End-");
- Console.ReadKey();
- }
And you can change databases on the fly:
The ’gotcha’ to realize is that Entity Framework requires that you have a default value in the app/web.config file. If not, you will get this exception:
This smells like too much coupling, but that is a common theme with EF. So make sure you have a default EF connection string in the .config – even if you don’t even use it.
Jamie Dixon's Home 