Cloud, MVC

Multi-tenancy with ASP.NET MVC and Entity Framework

Note: this blog is no longer maintained. Please go here to see the latest updates of this post.

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 have created a custom filter in ASP.NET MVC:

public class UserFilter : ActionFilterAttribute
 {
 ///
 /// Set session data for current user:
 /// - Current user
 /// - Tenant of the current user
 ///
 ///
 public override void OnActionExecuting(ActionExecutingContext filterContext)
 {
 // User must be logged in before setting session data
 if (filterContext.HttpContext.User.Identity.IsAuthenticated)
 {
 // Ensure session data compares to the actual current user
 if (filterContext.HttpContext.Session["User"] as string != filterContext.HttpContext.User.Identity.Name)
 {
 filterContext.HttpContext.Session["User"] = filterContext.HttpContext.User.Identity.Name;
 filterContext.HttpContext.Session["Tenant"] = null;
 }

 // Set tenancy identity for the current user
 if (filterContext.HttpContext.Session["Tenant"] == null)
 {
 string directoryPath = filterContext.HttpContext.Server.MapPath("~/bin");
 using (ServiceFactory factory = new ServiceFactory(directoryPath))
 {
 string[,] nullconstructor = new string[,]
 {
 {
 "Tenant",
 null
 }
 };

 using (IRepository tenantRepository = factory.Create(nullconstructor))
 {
 // Get current user from database and check to which tenant it belongs
 m.Tenant currentTenant = tenantRepository.FindOne(x => x.AspNetUsers.Select(y => y.Email).Contains(filterContext.HttpContext.User.Identity.Name));
 string tenantCode = currentTenant != null ? currentTenant.Code : string.Empty;

 filterContext.HttpContext.Session["Tenant"] = tenantCode;
 }
 }
 }
 }
 }
 }

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:

public ActionResult Index()
 {
 IEnumerable cars = this.Worker.FindAll();
 return View(cars);
 }

The following section will do the nitty gritty work using MEF and the repository pattern:

public IEnumerable FindAll()
 {
 using (IServiceFactory factory = new ServiceFactory(this.DirectoryPath))
 {
 ILogger logger = factory.Create();
 try
 {
 using (IRepository repository = factory.Create(this.ConstructorImports))
 {
 List items = repository.FindAll(null).ToList();
 return items;
 }
 }
 catch (Exception ex)
 {
 logger.LogError("Error when retrieving items.", ex);
 return new List();
 }
 }
 }

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:

protected string[,] ConstructorImports
 {
 get
 {
 return new string[,]
 {
 {
 "Tenant",
 this.Tenant
 }
 };
 }
 }

In the MEF plugin, this will be captured as follows:

public Repository([Import("Tenant")] string Tenant)
 : base(Create(Tenant, new SqlConnection(Settings.Default.Connectionstring)))
 {
 }

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:

public static FleetContext Create(string tenantSchema, DbConnection connection)
 {
 Database.SetInitializer(null);
 DbCompiledModel compiledModel = modelCache.GetOrAdd(
 Tuple.Create(connection.ConnectionString, tenantSchema),
 t =>
 {
 DbModelBuilder builder = new DbModelBuilder();
 builder.Conventions.Remove();

 builder.Configurations.Add(new CarMap(tenantSchema));
 builder.Configurations.Add(new ModelsMap(tenantSchema));
 builder.Configurations.Add(new PersonsMap(tenantSchema));
 builder.Configurations.Add(new ManufacturerMap(tenantSchema));
 builder.Configurations.Add(new ContractsMap(tenantSchema));
 builder.Configurations.Add(new CustomersMap(tenantSchema));
 builder.Configurations.Add(new InsurersMap(tenantSchema));
 builder.Configurations.Add(new VehiclesMap(tenantSchema));
 builder.Configurations.Add(new LogMap(tenantSchema));

 var model = builder.Build(connection);
 return model.Compile();
 });

 return new FleetContext(connection, compiledModel);
 }

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!)

tenant1

tenant2

tenantDB

Advertisements

5 thoughts on “Multi-tenancy with ASP.NET MVC and Entity Framework

    1. 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.

      Like

      1. 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 ?

        Like

  1. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s