February 15, 2016

ASP.NET Core Multi-tenancy: Data Isolation with Entity Framework

This is my fourth post in a series on building multi-tenant applications with ASP.NET Core.

A common requirement of multi-tenancy is to partition application services per tenant. This could be something presentational (like the theme-able engine I created in the previous article) or as I’ll cover in this post, how to isolate tenant data.

Multi-tenant Data Architecture

The three most common approaches to managing multi-tenant data are:

  1. Separate Database
  2. Separate Schema
  3. Shared Schema

These approaches vary in the level of isolation and complexity. Separate Database is the most isolated but will also make provisioning and managing your tenant data more complex. Licensing costs may also be a factor.

If you have a large number of clients with small datasets that have the same schema I’d personally recommend the Shared Schema approach, otherwise go for Separate Database. I’ve never found Separate Schema (same database) to be a good solution since you don’t really achieve better isolation and it makes maintenance a nightmare with a large number of clients.

For more information on multi-tenant data architecture, see this MSDN article.

Entity Framework Core

EF Core (7.0) is the evolution of Microsoft’s data-access tool and has been rebuilt from the ground up to be with a focus on performance and portability.

We’ll be using EF Core in this post to isolate tenant data using the database-per-tenant approach.

Getting Started

I’ll be building on the sample MVC application from the other posts. It uses ASP.NET Identity and is currently configured to use the same database for all tenants.

Tenant Connection Strings

Since we want to use a separate database for each tenant we will need a different connection string per tenant. We’ll add a ConnectionString property to AppTenant:

public class AppTenant
{
    public string Name { get; set; }
    public string[] Hostnames { get; set; }
    public string Theme { get; set; }
    public string ConnectionString { get; set; }
}

Then we can update appsettings.json to include our tenant-specific connection strings:

"Multitenancy": {
  "Tenants": [
    {
      "Name": "Tenant 1",
      "Hostnames": [
        "localhost:6000",
        "localhost:6001",
        "localhost:51261"
      ],
      "Theme": "Cerulean",
      "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=saaskit-sample-tenant1;"
    },
    {
      "Name": "Tenant 2",
      "Hostnames": [
        "localhost:6002"
      ],
      "Theme": "Darkly",
      "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=saaskit-sample-tenant2;"
    }
  ]
}

With that done, we need to configure Entity Framework to use our tenant-specific connection strings.

Configuring Entity Framework

As our sample project was created using the boilerplate ASP.NET Core MVC template, it is already configured to use a default connection string from appsettings.json. The following code in Startup.cs registers the necessary EF dependencies with the built-in services container:

// Add framework services.
services.AddEntityFramework()
    .AddSqlServer()
    .AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

The first step is to remove the default connection string. This will be configured using a different approach:

services.AddEntityFramework()
    .AddSqlServer()
    .AddDbContext<SqlServerApplicationDbContext>();

Note that I’ve also renamed ApplicationDbContext to SqlServerApplicationDbContext. You’ll see why later.

There are three different ways to configure a DbContext instance. OnConfiguring is executed last and overrides the options obtained from DI or the DbContext constructor. Given that DbContext instances are typically transient or created per-request we can get the current tenant instance and configure the connections string:

public class SqlServerApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    private readonly AppTenant tenant;

    public SqlServerApplicationDbContext(AppTenant tenant)
    {
        this.tenant = tenant;
        Database.EnsureCreated();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(tenant.ConnectionString);
        base.OnConfiguring(optionsBuilder);
    }
}

By taking a dependency on AppTenant the current tenant instance will be injected automatically. The Database.EnsureCreated() call will create the database if it does not already exist - perfect if you need to provision tenants on demand.

When we run the application we can see a new database is created for each tenant.

image

ASP.NET Identity continues to work as before only now our users are completely isolated per tenant.

### Using SQLite

My favourite thing about ASP.NET Core is that it’s cross platform. Being able to enjoy the richness of the .NET framework from my Mac using a lightweight editor like VS Code makes me very happy indeed.

For this reason I thought I’d update the sample to use SQLite instead of SQL Server. I find that a lightweight database such as SQLite works well for development since there is no additional software to install and you can blow away your databases just by deleting a file on disk.

First we’ll add the SQLite package to our dependencies project.json:

"dependencies": {
  "EntityFramework.Commands": "7.0.0-rc1-final",
  "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
  "EntityFramework.Sqlite": "7.0.0-rc1-final",
  ...

If you’re not using Visual Studio you can run dnu restore to pull down the package.

We’ll then create a different DbContext implementation that configures Entity Framework to use SQLite:

public class SqliteApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    private readonly IApplicationEnvironment env;
    private readonly AppTenant tenant;

    public SqliteApplicationDbContext(IApplicationEnvironment env, AppTenant tenant)
    {
        this.env = env;
        this.tenant = tenant;
        Database.EnsureCreated();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var tenantDbName = tenant.Name.Replace(" ", "-").ToLowerInvariant();
        var connectionString = $"FileName={Path.Combine(env.ApplicationBasePath, "App_Data", tenantDbName)}.db";
        optionsBuilder.UseSqlite(connectionString);

        base.OnConfiguring(optionsBuilder);
    }
}

Since SQLite is file-based we need to provide a physical path to the database. To do this we’re taking a dependency on IApplicationEnvironment so that we can obtain the application path at runtime. I chose to name the database based on the tenant name. You could also use a convention based approach for SQL Server rather than having to store the connection string for each tenant.

Finally we need to tell EF to use our SqliteApplicationDbContext instead. In Startup.cs:

services.AddEntityFramework()
    .AddSqlite()
    .AddDbContext<SqliteApplicationDbContext>();

services.AddIdentity<ApplicationUser, IdentityRole>()
    .AddEntityFrameworkStores<SqliteApplicationDbContext>()
    .AddDefaultTokenProviders();

Firing up a terminal on my Mac I can run the sample application with dnx web:

Jasper:AspNetMvcSample ben$ dnx web
Hosting environment: Production
Now listening on: http://localhost:60000
Now listening on: http://localhost:60001
Now listening on: http://localhost:60002
Application started. Press Ctrl+C to shut down.

When registering a user account on each of my tenant sites the SQLite databases are created automatically:

image

Wrapping Up

In this post we looked at how to isolate tenant data via a database-per-tenant strategy using Entity Framework. We updated the EF DbContext to obtain the connection string dynamically from the current tenant instance. Finally we swapped out SQL Server for SQLite making it possible to run the sample completely cross-platform.

Questions?

Join the SaasKit chat room on Gitter.


More content like this?

If you don’t have anything to contribute but are interested in where SaasKit is heading, please subscribe to the mailing list below. Emails won’t be frequent, only when we have something to show you.

Email Address

© 2022 Ben Foster