Until recently I have never had the time to concentrate on cloud application development. All of the projects (non-SharePoint related) I have ever worked on were dedicated to only one customer, so the multi tenancy challenge never came up. Although my own website is hosted on Azure, I realized I had to create my own project to gain some knowledge on this area. There are many books and web pages available regarding cloud development, but for a very good reason none of those give really specific examples: it all depends on what you want to achieve. In this article, I will cover one scenario that is quickly to implement with the knowledge many .NET developers probably still have, which is developing a **ASP.NET MVC application with Entity Framework which ensures storage in one database with multiple schemas.
By giving some code excerpts, I hope to give you some rough ideas how to make your own multi-tenant applications. If you want to create multi tenancy applications for thousands or millions of tenants, this approach would not be the best, instead I would consider technologies such as NoSQL or Windows Azure Table storage. However, for smaller applications serving a couple of hundreds of clients, this will do just fine. Each layer in the application’s architecture must be able to separate the tenants from each other. In my case as you will see later, I only need to pay attention to two things:
- Handling the current user and to which tenant he belongs: this is handled with standard MVC and Entity Framework.
- Resolving the current tenant with the database schema: this is handled with a slightly modified Entity Framework
The following sections will cover these two topics.
Resolving users to tenants
This section can be implemented quite easily. I have one database that uses the default ASP.NET Identity Framework. I have added two additional tables to cover the tenancy aspect:
- Tenant: stores general information about the tenant, such as the name and billing info but also a unique schema code.
- UsersPerTenant: mapping table between the tenant ID and user ID
In order to resolve the user to the tenant to which he belongs, I created a custom filter in ASP.NET MVC:
This piece of code queries the database to look for the mapping between the current user and the tenant and does stores it into the session. Please note that I use MEF and the Repository pattern to query the database. This information will then be used to query the tenant’s transactional data – which is stored in another database.
Resolving tenants to the database
The following controller action will retrieve a list of cars from the database from the tenant’s schema:
The following section will do the nitty gritty work using MEF and the repository pattern:
After having read the section above, you might be wondering where I am separating the tenant from each other. With MEF, it is possible to inject certain constructor parameters. In this case, I have done just so by injecting the tenant’s schema code in the repository plugin:
In the MEF plugin, this will be captured as follows:
This part is where it gets interesting. By using the incoming parameter, we can determine which schema to use and how to generate a DbContext class. The Create method you see in the constructor looks like this:
This method will create the model and generate a connection to the requested schema. This way all repository methods liking creating, updating, querying or deleting items will only happen on this schema (as if there are no others in this database). The final results look like this (note: the content is rubbish so nevermind the errors!) 
[…] you might have read in my previous post, I used SQL schemas to partition data between tenants. Being part of the true definition of SaaS, […]
Nice post
How migrate all schemas with code first?
Like you would do normally with the migrations commands. I haven’t figured it out yet but it could be possible that you’ll have to copy/paste the code in the migrations file for each schema you want to update and then change the schema in the strings.
Like so:
CreateTable(
“dbo.Contracts”,
c => new
{
Id = c.Int(nullable: false, identity: true),
StartDate = c.DateTime(nullable: false),
EndDate = c.DateTime(nullable: false),
Maximum = c.Int(nullable: false),
})
.PrimaryKey(t => t.Id);
CreateTable(
“newSchema.Contracts”,
c => new
{
Id = c.Int(nullable: false, identity: true),
StartDate = c.DateTime(nullable: false),
EndDate = c.DateTime(nullable: false),
Maximum = c.Int(nullable: false),
})
.PrimaryKey(t => t.Id);
Alternatively, you could use SQL Server Data tools to upgrade your database. This is much more intuitive and your upgrades will be easier to maintain. When deploying your new data models afterwards (= web app), you won’t need any migrations. It all depends on your preferences.
Hi, thanks for answer
And force migration in connection ?
var migration_config = new Configuration();
migration_config.TargetDatabase = new DbConnectionInfo(“Context”);
var migrator = new DbMigrator(migration_config);
migrator.Update();
In Configuration:
AutomaticMigrationsEnabled = true;
AutomaticMigrationDataLossAllowed = true;
I’m dont test
link reference: http://stackoverflow.com/questions/20907826/entity-framework-code-first-migration-to-multiple-database
Sugestions ?
What are you trying to achieve here, are you using multiple databases or multiple schemas? If you’re using multiple databases, you’ll need to specify your connectionstrings. Adding a constructor on your DbContext class could help:
public MyDbContext(string connectionstringName)
: base(“name=” + connectionstringName)
{
}
If you want to use the command line, you could specify your connection string there:
Update-Database -ConnectionStringName “MyConnectionString”
This will override the default connection string that is in your app.config or web.config.
Have you heard of the KISS principle?