Building ASP.NET Core and EF Core multi-tenant apps – Part1: the database

Multi-tenant applications are everywhere – your online banking is one example and GitHub is another. They consist of one set of code that is used by every user, but each user’s data is private to that user. For instance, my online banking app is used by millions of users, but only I (and the bank) can see my accounts.

This first article focuses on how you can keep each user’s data private from each other when using EF Core. The other articles in “Building ASP.NET Core and EF Core multi-tenant apps” series are:

1. The database: Using a DataKey to only show data for users in their tenant (this article)
2. Administration: different ways to add and control tenants and users
3. Versioning your app: Creating different versions to maximise your profits
4. Hierarchical multi-tenant: Handling tenants that have sub-tenants
5. Advanced techniques around ASP.NET Core Users and their claims
6. Using sharding to build multi-tenant apps using EF Core and ASP.NET Core
7. Three ways to securely add new users to an application using the AuthP library
8. How to take an ASP.NET Core web site “Down for maintenance”
9. Three ways to refresh the claims of a logged-in user

Also read the original article that introduced the library called AuthPermissions.AspNetCore library (shortened to AuthP in these articles) which provide pre-built (and tested) code to help you build multi-tenant apps using ASP.NET Core and EF Core.

TL;DR; – Summary of this article

• This article focuses on setting up a database to use in a multi-tenant application so that each tenant’s data is separate from other tenants. The approach described uses ASP.NET Core, EF Core and the AuthP library.
• The AuthP services and example code used in this article covers:
  • Create, update and delete an AuthP tenant
  • Assign a AuthP user to a tenant, which causes a DataKey Claim to be added to a logged-in user.
  • Provides services and code to set up an EF Core global query filter to only return data that matches the logged-in user’s DataKey claim.
• You provide:
  • Access to a database using EF Core.
  • An ASP.NET Core front-end application to display and manage the data
  • You need to choose an ASP.NET Core authentication provider to manage the user who can log into your application.
• There is an example ASP.NET Core multi-tenant application in the AuthP repo, called Example3 which uses ASP.NET Core’s individual accounts authentication provider. The Example3 web site will migrate/seed the database on startup to load demo data to provide you with a working multi-tenant application to try out.
• Here are links to various useful information and code.
  • The AuthP’s documentation (and a few videos)
  • The AuthP’s repo, with roadmap on the future of the AuthP library
  • The AuthP NuGet library – see the ReleaseNotes file for the changes in each release.

Defining the terms in this article

We start with defining key terms / concepts around multi-tenant application. This will help you as your read these articles.

The first term is multi-tenant, which is the term used for an application that provides the same services to multiple customers – known as tenants. Users are assigned to a tenant and when a user logs in, they only see the data linked to their tenant.

The figure below shows a diagram of the Example3 application in the AuthPermissions.AspNetCore repo that provides invoice management. Your application single database, which is divided between three tenants are shown, brown, blue and green. By assigning a unique DataKey to each tenant the application can filter the data in the database and only show invoices rows that have the same DataKey as the user has.

The positives of the multi-tenant design it can many customers on one application / database which reduces the overall costs of your hardware / cloud infrastructure. The downside is more complex code to keep each tenant’s data private, but as you will see EF Core has some great features to help with keeping data private.

NOTE: The broader term of Software as a Service (SaaS) can cover multi-tenant application, but SaaS also cover a single instance of the application for each user.

There are two main ways to separate each tenant’s data:

• All tenant’s data on one database, with software filtering of each tenant data access, which is what this article uses.
• Each tenant has its own database, and software directs each tenant to their database. This is known as sharding. 

There are pros and cons for both approaches (you can read this document on the pros/cons on a software filtering approach). The AuthP library implements software filtering approach with specific code to ensure that each tenant is isolated from each other.

How to implement a single multi-tenant application with software filtering

There are eight steps to implementing a ASP.NET Core multi-tenant application using EF Core and the AuthP library. They are:

1. Register the AuthP library in ASP.NET Core: You need to register the AuthP library in your ASP.NET Core Program class (net6), or Startup class for pre-6 code.
2. Create a tenant: You need a tenant class that holds the information about that tenant, especially the tenant value, referred to as the DataKey, that is used to filter the data for this tenant.
3. Assign User to Tenant: You then need to a way to link a user to a tenant, so that the tenant’s DataKey is assigned to a user’s claims when they log in.
4. Inject the user’s DataKey into your DbContext: You create a service that can extract the user’s DataKey claim value from a logged-in user and inject that service into your DbContext.
5. Mark filtered data: You need to add a property to each class mapped to the database (referred to as entity classes in this article) that holds the DataKey.
6. Add EF Core’s query filter: In the application’s DbContext you set up a global query filter for each entity class that is private to the tenant.
7. Mark new data with user’s DataKey: When new entity classes are added you need to provide a way to set the DataKey with the correct value for the current tenant.
8. Sync AuthP and your application’s DbContexts – To ensure the AuthP DbContext and your application’s DbContext are in sync the two DbContexts must go to one database. This also saves you the cost of using two databases.

This article uses the AuthP’s library so some of these steps are provided by that library, with you writing code to link the AuthP’s feature to your code that provides the specific features you provide to your customers.

1. Register the AuthP library in ASP.NET Core

To use the AuthP library you need to do three things:

• Add the AuthPermissions.AspNetCore NuGet package in your ASP.NET Core project, and any other projects that need to access the AuthP library.
• You need to create the Permissions enum that will control what features your users can access to your application features – see this section of the original article in the AuthP library, or the Permissions explained documentation.
• Register AuthP to the ASP.NET Core dependency injection (DI) provider, which when using net6 is in the ASP.NET Core program file.

I’m not going to go through all the AuthP’s registering options because there are lots of different options, but below I show a simplified version of the Example multi-tenant application. For full information on the setup of the AuthP library go to the AuthP Starup code document page which covers all the possible options and a more detailed coverage of the SetupAspNetCoreAndDatabase method that manages the migration of the various databases.

services.RegisterAuthPermissions<Example3Permissions>(options =>
    {
        options.TenantType = TenantTypes.SingleLevel;
        options.AppConnectionString = connectionString;
        options.PathToFolderToLock = _env.WebRootPath;
    })
    .UsingEfCoreSqlServer(connectionString)
    .IndividualAccountsAuthentication()
    .RegisterTenantChangeService<InvoiceTenantChangeService>()
    .RegisterFindUserInfoService<IndividualAccountUserLookup>()
    .RegisterAuthenticationProviderReader<SyncIndividualAccountUsers>()
    .SetupAspNetCoreAndDatabase(options =>
    {
        //Migrate individual account database
        options.RegisterServiceToRunInJob<StartupServiceMigrateAnyDbContext<ApplicationDbContext>>();

        //Migrate the application part of the database
        options.RegisterServiceToRunInJob<StartupServiceMigrateAnyDbContext<InvoicesDbContext>>();
    });

The various lines to look at are

• Line 3. You need to tell AuthP that your application uses a normal (single-level) multi-tenant
• Line 4. The AppConnectionString is used in step 8 when you want to create, update or delete a tenant
• Line 5. To handle migrating (and seeding) databases on startup it needs a global resource to create a lock. In cases where the database doesn’t exist yet, the backup is to lock a global directory, so I provide a path to the WebRootPath – see step 8

2. Create a tenant

The AuthP library contains the IAuthTenantAdminService service which contains the AddSingleTenantAsync method that creates a new tenant using the tenant name you provide (and must be unique). Once the new tenant is created its DataKey can be accessed using the tenant’s GetTenantDataKey method, which returns a string with the tenant primary key and a full stop, e.g. “123.”.  

3. Assign a AuthP User to Tenant

Again, the AuthP library has its own set of user information which includes a (optional) link to a Tenant and the user’s Roles / Permissions (covered in the first AuthP article). To add a new user and link them to a tenant, you must provide the tenant they should be linked to. There are various ways to add a user and I cover that that in the Part 2 article, which is about administration and adding user, including an approach that lets an admin user send an invite to join the tenant via person’s email.

Once a user is linked to a tenant, then when that user logs in a DataKey claim is added to their claims.

4. Inject the user’s DataKey into your DbContext

You want to transfer the DataKey claim into your application’s DbContext so that only database rows that has the same DataKey at the user’s DataKey claim as readable (see step 6).

In ASP.NET Core the way to do this is via the IHttpContextAccessor service. The code below, which is provided by the AuthP library, extracts the DataKey of the current user.

public class GetDataKeyFromUser : IGetDataKeyFromUser
{
    /// <summary>
    /// This will return the AuthP' DataKey claim. 
    /// If no user, or no claim then returns null
    /// </summary>
    /// <param name="accessor"></param>
    public GetDataKeyFromUser(IHttpContextAccessor accessor)
    {
        DataKey = accessor.HttpContext?.User
              .GetAuthDataKeyFromUser();
    }

    /// <summary>
    /// The AuthP' DataKey, can be null.
    /// </summary>
    public string DataKey { get; }
}

Then, in your application’s DbContext constructor you add parameter of type IGetDataKeyFromUser, which the .NET dependency injection (DI) provider will provide the GetDataKeyFromUser class shown above, which was registered a to the DI provider by the AuthP library.

The InvoicesDbContext code below comes from Example3 in the AuthP repo and shows the start of a DbContext that has a second parameter to get the user’s DataKey.

public class InvoicesDbContext : DbContext
{
    public string DataKey { get; }

    public InvoicesDbContext(
        DbContextOptions<InvoicesDbContext> options,
        IGetDataKeyFromUser dataKeyFilter)
        : base(options)
    {
        DataKey = dataKeyFilter?.DataKey ?? "Impossible DataKey";
    }
    //… other code left out
}

The DataKey could be null if no one logged in or the user hasn’t got an assigned tenant (or other situations such as on startup or background services accessing the database), so you should provide a string that won’t match any of the possible DataKeys. As AuthP only uses numbers and a full stop any string containing alphabetical characters won’t match any DataKey.

5. Mark filtered data

When creating a multi-tenant application, you need to add a DataKey to each class that is used in the tenant database (referred to tenant entities). I recommend adding an interface that contains a string DataKey to each tenant entity – as you will see in the next step that helps. The code below is taken from the Invoice class in the Example3.InvoiceCode project.

public class Invoice : IDataKeyFilterReadWrite
{
    public int InvoiceId { get; set; }

    public string DataKey { get; set; }
    
    //other properties left out
}

NOTE: The IDataKeyFilterReadWrite interface says that you can read and write the DataKey. If you are using DDD entities, it would need a different interface.

6. Add EF Core’s query filter

Having an interface on each tenant entity allows you to automate the setting up of the EF Core’s query filter, and its database type, size and index. This automated approach makes sure you don’t forget to apply the DataKey query filter in your application, because a missed DataKey query filter creates a big hole the security of your multi-tenant application.

The code below is taken from Example3.InvoiceCode’s InvoicesDbContext class and shows a way to automate the query filter.

public class InvoicesDbContext : DbContext, IDataKeyFilterReadOnly
{
    public string DataKey { get; }

    public InvoicesDbContext(DbContextOptions<InvoicesDbContext> options, 
        IGetDataKeyFromUser dataKeyFilter)
        : base(options)
    {
        DataKey = dataKeyFilter?.DataKey ?? " Impossible DataKey ";
    }

    //other code removed…

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // … other configurations removed  

        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            if (typeof(IDataKeyFilterReadWrite)
                 .IsAssignableFrom(entityType.ClrType))
            {
                entityType.AddSingleTenantReadWriteQueryFilter(this);
            }
            else
            {
                throw new Exception(
                    $"You missed the entity {entityType.ClrType.Name}");
            }
        }
    }
}

Lines 20 to 29 set up the DataKey of your entities and 27 throws an exception if any entity doesn’t implement the IDataKeyFilterReadWrite interface. This ensures that you don’t forget to add the IDataKeyFilterReadWrite to every entity class in your application’s database.

The AddSingleTenantReadWriteQueryFilter extension method is part of the AuthP library and does the following:

• Adds query filter to the DataKey for an exact match to the DataKey provided by the service IGetDataKeyFromUser described in step 3.
• Sets the string type to varchar(12). That makes a (small) improvement to performance.
• Adds an SQL index to the DataKey column to improve performance.

UPDATE Jan 2023

NOTE: Steven pointed out in this issue that the AddSingleTenantReadWriteQueryFilter in the AuthP library doesn’t work on Table-per-hierarchy (TPH) entities. The AddSingleTenantReadWriteQueryFilter can be replaced by new code quite easily and Steven has describes what you need to to support TPH entities. I haven’t checked the TPT and TPC entity types, but I would expect them to work with the AddSingleTenantReadWriteQueryFilter as they have their own tables.

7. Mark new data with user’s DataKey

When a tenant user adds a new row in the tenant database, it MUST set the DataKey of the current user that is saving to the database. If you don’t the data will be saved, but no one will see it. The typical approach is to override the SaveChanges(bool) and SaveChangesAsync(bool, CancellationToken) versions of the SaveChanges / SaveChangesAsync, as shown below (but you could also use EF Core’s SaveChanges Interceptor).

public override int SaveChanges(
    bool acceptAllChangesOnSuccess)
{
    this.MarkWithDataKeyIfNeeded(DataKey);
    return base.SaveChanges(acceptAllChangesOnSuccess);
}

public override async Task<int> SaveChangesAsync(
    bool acceptAllChangesOnSuccess,
    CancellationToken cancellationToken = default(CancellationToken))
{
    this.MarkWithDataKeyIfNeeded(DataKey);
    return await base.SaveChangesAsync(
        acceptAllChangesOnSuccess, cancellationToken);
}

The MarkWithDataKeyIfNeeded extension method in Example3 sets the DataKey property of any new entities with the current user’s DataKey string. The code below shows how that is done.

public static void MarkWithDataKeyIfNeeded(
    this DbContext context, string accessKey)
{
    foreach (var entityEntry in context.ChangeTracker.Entries()
        .Where(e => e.State == EntityState.Added))
    {
        var hasDataKey = entityEntry.Entity 
             as IDataKeyFilterReadWrite;
        if (hasDataKey != null && hasDataKey.DataKey == null)
            // The DataKey is only updatedif its null
            // This allow for the code to defining the DataKey
            hasDataKey.DataKey = accessKey;
    }
}

8. Make sure AuthP and your application’s DbContexts are in sync

It is very important that the AuthP database and your application’s database are kept in sync. For instance, if AuthP creates a new tenant and then your application’s DbContext had a problem creating that tenant, then you have a mismatch – AuthP thinks that the tenant data is OK, but when you try to access that data, you will get an error (or worse, you don’t get an error, but the data is wrong).

For this reason, the AuthP requires that:

1. You must create a ITenantChangeService service, which you register at startup with AuthP via the RegisterTenantChangeService< ITenantChangeService>() method. This has methods to handle create, update and delete of your application’s DbContext. If you want an example have a look at the InvoiceTenantChangeService class from Example3.
2. Your application’s DbContext and the AuthP’s DbContext must be on the same database. This allows the AuthP tenant code to use a transaction that contains the AuthP database changes and ITenantChangeServices applied to within your application’s DbContext. If the changes to your application’s database fail, then the AuthP changes are rolled back.

The figure below shows the steps that AuthP when you create, update or delete a AuthP tenant.

Linking two DbContexts to one database requires you have to define a unique migration history table. By doing that you can migrate each DbContexts separately. The code below, taken from Example3, shows how to register a DbContext and defining the migration history table name for this DbContext.

services.AddDbContext<InvoicesDbContext>(options =>
    options.UseSqlServer(
        configuration.GetConnectionString("DefaultConnection"), 
           dbOptions =>dbOptions
               .MigrationsHistoryTable(InvoicesDbContextHistoryName)));

NOTE: The AuthP’s DbContext uses the name “__AuthPermissionsMigrationHistory” for its migration history table.

When it comes to migrating the AuthP’s DbContext and your application’s DbContext (and the Individual Accounts database if you use that) you have a few approaches. You can apply migrations in your CI/CD pipeline using EF Core 6’s migrate.exe approach. But with the release of version 2 of the AuthP library you can migrate databases on startup within a global lock (please read this article).

AuthP version 2 uses the Net.RunMethodsSequentially library so that you can migrate EF Core databases even if your application is running multiple instances. Here is the Example3 code that register of the AuthP library in its Startup class and this documentation page covers migrations in detail.

services.RegisterAuthPermissions<Example3Permissions>(options =>
    {
        options.TenantType = TenantTypes.SingleLevel;
        options.AppConnectionString = connectionString;
        options.PathToFolderToLock = _env.WebRootPath;
    })
    .UsingEfCoreSqlServer(connectionString)
    //Other registration methods left out for clarity
    .RegisterTenantChangeService<InvoiceTenantChangeService>()
    .SetupAspNetCoreAndDatabase(options =>
    {
        //Migrate individual account database
        options.RegisterServiceToRunInJob<StartupServiceMigrateAnyDbContext<ApplicationDbContext>>();
        //Migrate the application part of the database
        options.RegisterServiceToRunInJob<StartupServiceMigrateAnyDbContext<InvoicesDbContext>>();
    });

NOTE: Line 9 registers your ITenantChangeService needed to keep your applications tenant data is in sync with what the AuthP’s tenant definitions.

This migrates:

• The AuthP database
• The individual account database (Example3 uses ASP.NET Core’s individual account provider)
• Example3’s application database

In fact there are other seeding of databases too, but I’m not showing them as some of them are only to add example data so that if you run the example it will look like real application with users  – look at the Startup class for the full detail of what it does.

NOTE: The same rules apply to EF Core 6’s migrate.exe approach and AuthP’s Net.RunMethodsSequentially approach: if there is an instance of the application running, then the migration must not include a migration that will not work on the previous and new version of your application.

Conclusion

This is a long article because it steps though all the things to need to do to build a multi-tenant application. Many stages are handled by the AuthP library or EF Core, and where that isn’t the case there are linked to example code from Example3 multi-tenant application in the AuthP’s repo. Also there is a lot of documentation (and a few videos) for the AuthP library and a plenty of runnable examples in the AuthP repo.

The AuthP library isn’t small and simple, but that’s because building real multi-tenant applications aren’t small or simple. I know, because I was asked by a client to design a multi-tenant application to handle thousands of users, and you need a lot of things to manage and support that many tenants and users.

So, on top of the documentation and examples I have starting this “Building ASP.NET Core and EF Core multi-tenant apps” series that takes you through various parts of a multi-tenant application, including some of the features I think you will need in a real multi-tenant application

Do have a look at the AuthP Roadmap, which gives you an idea of what has been done, and what might change in the future. Also, please do give me feedback on what you want more information or features you need, either via comments on these articles or add an issue to the AuthP’s repo.

Happy coding.