Using multiple Databases with single DbContext and Entites and generating Conn String at runtime

Question

I am developing an MVC 5 application. Initially I was using single database with EF6 DataBase first approach and I am using my DbContext instance to access my database which have 102 tables. I am declaring its instance as:

private MyEntities db = new MyEntities();

Now I want to allow multiple companies to use my application and for that matter I have to create a new database for each new company. I achieved this by declaring another constructor to my DbContext as follows:

public partial class NewEntities : DbContext
{
    public NewEntities(string name)
        : base(name)
    {
    }
}

And then declaring its instance as:

public NewEntities de = new NewEntities((ConfigurationManager.ConnectionStrings["NewEntities123"]).ToString());

Then I called

db.Database.Create();

and my new database was created successfully. But in this scheme I have to declare a new connection string in my Web.config file each time I want to add a database.

Is there any method where a connection string is generated automatically as per the company name in config file and then passed to the constructor to create a new database with that name?

Moreover I want to access all my controller methods with the same "db" instance and DbContext for every company, so that my same code can be used for all companies. How can I access single DbContext and its instance for multiple databases?

I have already looked answer to this as it is saying that there is no method. But then How can my application work for multiple Users?

Answer 1

When working database-first, having an edmx file with mappings, you can create and use database this way.

add an overload to your context constructor (as you did), for example:

public TenantDBContainer(string connectionString)
    : base(connectionString)
{
}

Then when you need to create database if not exists, using yourContextInstance.Database.CreateIfNotExists();

var connectionStringTemplate =
    @"metadata=res://*/Models.TenantDB.csdl|res://*/Models.TenantDB.ssdl|res://*/Models.TenantDB.msl;" +
    @"provider=System.Data.SqlClient;" +
    @"provider connection string=""data source=(localdb)\v11.0;" +
    @"initial catalog={0};"+
    @"user id={1};password={2};" +
    @"MultipleActiveResultSets=True;App=EntityFramework"";";

var TenandDBName = "Database Name Based on Tenant";
var TenantUserName = "UserName Based on Tenant";
var TenantPassword = "Password Based on Tenant";
var connectionString = string.Format(connectionStringTemplate, TenandDBName, TenantUserName, TenantPassword);
var db = new TenantDBEntities(connectionString);
db.Database.CreateIfNotExists();

Note:

• This way in a multi-tenant application you can create database per tenant and use it.
• You don't need to change your webconfig to add connection string there, you can simply create connection string at run-time.
• Now you can design an structure to get context based on your tenant detection strategy.

As an example to simplify the case, suppose you have a static method somewhere that returns an instance of context for you, for example:

Public class DbHelper
{
    public static TenantDBEntities GetDbContext(string tenantName)
    {
        var connectionStringTemplate =
            @"metadata=res://*/Models.TenantDB.csdl|res://*/Models.TenantDB.ssdl|res://*/Models.TenantDB.msl;" +
            @"provider=System.Data.SqlClient;" +
            @"provider connection string=""data source=(localdb)\v11.0;" +
            @"initial catalog={0};"+
            @"integrated security=True;" +
            @"MultipleActiveResultSets=True;App=EntityFramework"";";

        var TenandDBName = "TenantDB_" + tenantName;
        var connectionString = string.Format(connectionStringTemplate, TenandDBName);
        var db = new TenantDBEntities(connectionString);
        db.Database.CreateIfNotExists();

        return db;
    }
}

And suppose my tenant dettection strategy is simply based on request url or something else.

Now for example in ProductController in Index action you can use it this way;

public ActionResult Index()
{
    var tenantName = "Get tenant based on your strategy, for example tenant1 or tenant2";
    var db= DbHelper.GetDbContext(tenantName)
    var model= db.Products.ToList();
    return View(model);
}

When each tenant is working with this action, it connects to database suitable for that tenant, and if the database not exists, first creates it then uses it.

This way each tenant will see his own products.

Answer 2

You could use a factory to do something similar to what you are trying to do. There would be no other way to just use one DbContext for multiple databases without specifying the database you are trying to connect to. The problem with what you are trying to do is that the entities under the context would need to exist in both databases. I think what you really want is a factory that produces multiple DbContext classes (second example).

    void Main()
    {
        var factory = new DBFactory();
        var context = factory.GetContext("NewEntities123");
        var context2 = factory.GetContext("SomeOtherDatabase");
    }

    public class DBFactory
    {
        public DBContext GetContext(string dbName)
        {
            return new NewEntities((ConfigurationManager.ConnectionStrings[dbName]).ToString());
        }
    }

void Main()
{
    //instead of passing in the database name here you could 
    //just use a single config value for a database and that
    //database would be different for your different apps
    //then it would just be  var context = factory.GetContext();
    var context = factory.GetContext("NewEntities123");
}

public class DBFactory
{
    public DBContext GetContext(string dbName)
    {
        switch(dbName)
        {
            case "NewEntities123":
                return new NewEntities((ConfigurationManager.ConnectionStrings[dbName]).ToString());
            case "SomeOtherDatabase":
                return new SomeOtherEntities((ConfigurationManager.ConnectionStrings[dbName]).ToString());
        }
    }
}