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?

Bruin

msdn.microsoft.com/en-us/library/… – Jehanna

I think that answer said it is impossible to use an instance of context for multiple database. But it is completely possible to use a context class to connect to multiple database with same schema that your context class have. – Anjelicaanjou

but if i won't use same instance ,, i have to change all my queries. since they all use that "db" instance. And multitenancy approach always allows single instance to serve – Bruin

I Know what you mean and I believe that my post is exactly what you need. It creates multiple databases at run-time when needed. Each database will be used for a tenant. – Anjelicaanjou

Yeah.. ur post successfully creates a new database.. but the problem again comes to the context.. how that single DbContext will point towards multiple databases.. There would be some kind of code or whatever to bind the same Context to all databases. – Bruin

You can check the answer fo update and see the example:) – Anjelicaanjou

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.

Anjelicaanjou

I have posted a question here... – Bruin

THANKS. The database is created without conn string .. Plz guide me towards the strategy to bind that database to my context.. Bcoz its again giving the error mentioned in my question. – Bruin

THANKS a lot ... It works... THANK u very much.. I have been trying it for many days.. hats Off – Bruin

Happy to hear that it helped you to solve the problem, You are welcome :) – Anjelicaanjou

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());
        }
    }
}

Intersidereal

Answer 3

The parameter passed into your DbContext constructor can either be a name or a connection string. If you just generate your connection string somewhere and pass it in when the class is created, everything should work correctly.

Aksum

In that way the database would be created but it won't use the same DbContext instance. I have to change the instance – Bruin