Part6: Using sharding to build multi-tenant apps using ASP.NET Core and EF Core

Last Updated: April 27, 2022 | Created: April 5, 2022

This article describes how to use EF Core and ASP.NET Core to create a multi-tenant application where each different groups (known as tenants) has its own database – this is known as sharding. A second section describes how to build a hybrid a multi-tenant design which supports multiple tenants in one database and tenants that has its own database, i.e. sharding. 

NOTE: Multi-tenant applications are also referred to as SaaS (Software as a Service). In these articles I use the term multi-tenant as SaaS can also cover having one application + database for each company / tenant.

This article is part of the series that covers .NET multi-tenant applications in general. Also the designs shown in this article comes from 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. 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
  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 (this article)

TL;DR; – Summary of this article

  • Multi-tenant applications provide a service to many tenants. Each tenant has their own set of data that is private to them.
  • Sharding is the name given to multi-tenant applications where each different tenant has its own database. The other approach is to put all the tenants in one database where each tenant has a unique key which makes sure only data marked with the tenant’s key is returned (known as shared-database).
  • The good parts of the sharding approach are its faster and data is more secure than the shared-database approach, but sharding comes with the price of having many databases.
  • There is a third, hybrid approach which supports both shared-database and sharding at the same time – this allows you to manage the cost / performance by putting a group of tenants with small data / usage into one database while tenants with high data / usage can have their own database.
  • I detail 7 steps to create a sharding multi-tenant application using EF Core and ASP.NET Core. The steps are a mixture of ASP.NET Core code and EF Core’s code.
  • Then I detail 8 steps (3 of which the same as the sharding approach) that implements the hybrid approach. This implementation has been added in version 3 of the AuthP library and there is an example called Example6.SingleLevelSharding which you can run.

Setting the scene – what is sharding and why it is useful?

Wikipedia says that database sharding “A database shard, or simply a shard, is a horizontal partition of data in a database or search engine. Each shard is held on a separate database server instance, to spread load”. I emphasized the last sentence because that’s the key part – a multi-tenant / SaaS application will have a database for each separate tenant. The alternative to using sharding is to store all the data in one database and the tenant’s data are differentiated by a unique key for each tenant – I will refer to as shared-database, while sharding is a dedicated-database approach or sharding, which is shorter.

There are a number of pros / cons to each approach, but the biggest is the cost verses performance issue. A sharding approach should be quicker than the shared-database approach, but sharding’s performance comes from having lots of databases, which costs more money. The other pro for the sharding approach is that each tenant’s data is more isolated from each other, as each tenant has its own database.

NOTE: This Microsoft document describes some other differences between sharding and shared-database, plus a comparison of three ways to provide a service to many tenants.

There is a third, hybrid approach that allows you to balance the cost /performance. This design uses sharding for tenants that has a lot of data / demand, while tenants with less data / demand go the shared-database approach. The benefit of this approach is you can offer smaller tenants a lower price by putting them in shared database, while tenants that have higher demands will pay for a dedicated database.

Some years ago, I was asked to design and build a multi-tenant application with thousands of tenants, with tenants ranging from a less than a hundred users to a few large tenants with thousands of users. My client wanted a hybrid approach to cover this wide range of tenant types, which is why I have added both sharding and the hybrid approach to my AuthP library.

Here is a diagram to show all three approaches with a summary of their pros and cons.

Finally, I should add that Azure has a useful feature called SQL Server Elastic Pools which can help with the cost / performance by providing an overall level of database performance which is shared across all the databases in the pool. I will talk more about that in the next article.

How I implemented sharding in version 3 of my AuthP library

In one of my tweets about building multi-tenant applications a number of people said they used sharding. AuthP version 2 only supports the shared-database approach, but this feedback made me make the focus of version 3 release of the library to implementing sharding for multi-tenant application.

In addition, the AuthP sharding feature is designed to support the hybrid approach as well, which means you can use the shared-database approach and / or dedicated-database (sharding) approach. As I have already explained, this allows you to balance the cost / performance for each tenant if you want to.

I have split the description of the EF Core / ASP.NET Core code into two parts:

  1. Implement a sharding-only multi-tenant application.
  2. Implement a hybrid multi-tenant application.

1. Implement a sharding-only multi-tenant application

The figure below shows what the sharding-only design would look like, with a database containing information about the users and tenants (top left) and a database for each tenant (bottom).

Here are the steps to implement sharding for a multi-tenant application:

  1. Decide on how to manage databases, especially in production
  2. Hold information of the tenant and its users in admin database
  3. When a user logs in, then add a ConnectionName claim to their claims
  4. Provide a service to convert the user’s ConnectionName claim to a database connection
  5. Provide the connection string to the application’s DbContext
  6. Use EF Core’s SetConnectionString method to set the database connection
  7. Migrate the tenant database if not used before

1. Decide on how to manage databases, especially in production

UPDATE: This section has been updated and matches AuthP version 3.2.0

When I looked at the issue of deploying a multi-tenant application that uses multiple database (and possible geographically database servers) I came up with a way that ensures that the private data, e.g. the username / password for the database server, was hidden, while allowing an admin user to add new databases. This approach split the problem into two parts:

  • How to define the database servers
  • How to define each database on a server
1a. How to define the database servers

It’s the database servers that hold the private data, and as such they need to be managed carefully. Thankfully ASP.NET Core and Azure have excellent ways (app secrets or Azure app configuration) to keep the connection strings private.

With this in mind I store the connection strings to the servers I want to use without the database name (plus the DefaultConnection where the AuthP stores its data) – see below:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=…, username/password, Database=XXX.",
    "WestCoastServer": "Server=… username/password (no database name)",
    "CentralServer": "Server=… username/password (no database name)",
    "EastCoastServer": "Server=… username/password (no database name)",
    …etc.
  },
//… other parts left out
}

You can either use ASP.NET Core’s secrets, or if you are using Azure I recommend Azure app Service configuration, which can be set up during building the Visual Studio’s Publish feature. Either of these approaches ensures that the private parts of the connection string are kept secret.

NOTE: Do NOT use Azure’s Key Vault as it has a limit of 200 requests / second and in a sharding design the connection string is accessed on every HTTP request that accesses a database.

1b. How to define the databases

I decided to define each database would be defined by four properties, and these properties would be known as a database information.

  • Name: This name is used as reference to database information.
  • ConnectionName: This contains the name of the connection string the “ConnectionStrings” section that contains the information to a database server.
  • DatabaseName: This holds the name of the database.
  • DatabaseType: This holds the database type, e.g. SqlServer, Postgres.

The database information for each database is then stored in a file called shardingsettings.json, which is registered with ASP.NET Core Configuration – see an example file below.

{
  "ShardingDatabases": [
    {
      "Name": "DatabaseWest1",
      "DatabaseName": "asp.net-Example6.Sharding_West1",
      "ConnectionName": "WestCoastServer",
      "DatabaseType": "SqlServer"
    },
    {
      "Name": "DatabaseCentral1",
      "DatabaseName": "asp.net-Example6.Sharding_Central1",
      "ConnectionName": "CentralServer",
      "DatabaseType": "SqlServer"
    },
    // other entries left out
  ]
}

The tenant has the database information’s Name and from this it does the following:

  1. Read the database information from the shardingsettings.json configuration.
  2. Read the connection string from the appsettings file with name provided from the ConnectionName from the database information loaded in step 1.
  3. Then the ShardingConnections service will add the DatabaseName form the database information into connection string to provide the full connection string to go to the tenant application’s DbContext.

NOTE: I use the IOptionsSnapshot<T> service when accessing both the appsettings file and the shardingsettings.json file. This means that it gets the latest information from both configuration data.

2. Hold information of the tenant and its users in admin database

The way a multi-tenant application is there are tenants, with many users linked to each tenant. Typically, the tenant will have a unique key, often a primary key provided by the admin database, a name, e.g. “Company XYZ”, and in this case it would contain the name of the database information name.

ASP.NET Core handles the authentication of a user and provides a unique id, often a string, for each user. You need to add extra data to link a user’s id to a tenant – one simple way would add a collection of users’ id using a one-to-many relationship.

NOTE: The AuthP library has built-in AuthUser and Tenant classes, with admin code to manage these and link to the ASP.NET Core authentication handler. This the AuthP documentation page called “Multi-tenant explained” for how that works.

3. When a user logs in, then add a ConnectionName claim to their claims

When a user logs in you need to detect if they are linked to a tenant, then you need to add a claim containing the connection string name. This requires you to intercept the login process and use the user’s id to obtain the connection string name held in the tenant admin class.

Intercepting the login process depends on the ASP.NET Core authentication handler you are using – the article called “Advanced techniques around ASP.NET Core Users and their claims” provides information on the main authentication handlers.

NOTE: The AuthP library automatically adds the ConnectionName claim if sharding is turned on.

4. Provide a service to convert the user’s ConnectionName claim to a database connection

Having decided to use the connection string name in the claim, you need a way to access the “ConnectionStrings” object in the appsetting file. At the same time, I want to be able to add new connection strings while the application is running. This means can’t use the normal IOption<T> options, but I have to use the IOptionsSnapshot<T> option which reads current data in the appsetting file.

I built a service called ShardingConnections which uses IOptionsSnapshot<T> to get the latest “ConnectionStrings”. The code below shows the specific parts of this service to get the connection string from a connection name (thanks to ferarias answer to this stack overflow question). This service should be set up as a scoped service.

public class ConnectionStringsOption : Dictionary<string, string> { }
public class ShardingConnections : IShardingConnections
{
    private readonly ConnectionStringsOption _connectionDict;

    public ShardingConnections(
        IOptionsSnapshot<ConnectionStringsOption> optionsAccessor)
    {
        _connectionDict = optionsAccessor.Value;
    }

    public string GetNamedConnectionString(string connectionName)
    {
        return _connectionDict.ContainsKey(connectionName) 
        ? _connectionDict[connectionName]
        : null;
    }
    //Other methods not shown
}

NOTE: The full service contains extra methods useful for the admin when assigning a connection name to a tenant.

5. Provide the connection string to the application’s DbContext

You need to inject a service into the tenant application’s DbContext which contains the connection string for the current user. To do that we need two parts:

  • Get the ConnectionName claim from the current user
  • Use the ShardingConnections service to get the connection string

The code below shows a Scoped service that uses the IHttpContextAccessor to get the logged-in user (if present) with its claims. From this it can obtain the ConnectionName claim and passes the connextion string name to the ShardingConnections’s GetNamedConnectionString method. This returns the connection string that the DbContext.

public class GetShardingData : IGetShardingDataFromUser
{
    public GetShardingDataUserNormal(IHttpContextAccessor accessor,
        IShardingConnections connectionService)
    {
        var connectionStringName = accessor.HttpContext?
             .User?.Claims.SingleOrDefault(x => 
                   x.Type == PermissionConstants.ConnectionNameType)?.Value
        if (connectionStringName != null)
            ConnectionString = connectionService
                .GetNamedConnectionString(connectionStringName);
    }

    public string ConnectionString { get; }
}

6. Use EF Core’s SetConnectionString method to set the database connection

The tenant application’s DbContext needs to link to the database that has been provided by the GetShardingData service in the last section. The  EF Core’s SetConnectionString method (added in EF Core 5) sets the connection string to be used for this instance of the DbContext. The code below shows the constructor on the DbContext than handles the tanant’s data.

public class ShardingSingleDbContext : DbContext
{
    public ShardingSingleDbContext(
        DbContextOptions<ShardingSingleDbContext> options,
        IGetShardingDataFromUser shardingData)
        : base(options)
    {
        Database.SetConnectionString
           (shardingData.ConnectionString);
    }
    //… other parts left out
}

NOTE: The EF Core team says using SetConnectionString doesn’t have much of an overhead so sharding shouldn’t be slowed down by changing databases. You may also be interested how you would use DbContext pooling when building multi-tenant applications.

7. Migrate a database if not used before

At some point you need to migrate a database that hasn’t been used before. In the AuthP library the creation of a new tenant causes a call a method in a service written by the developer that follows the ITenantChangeService interface. When working with sharding I added the following code to check the database exists and migrate if it has no tables in it. It returns an error string if the database isn’t found, or null if it finished successfully.

NOTE: This stack overflow question has lots of useful ways to detect if a database exist and so on.

private static async Task<string> CheckDatabaseExistsAndMigrateIfNew(
     ShardingSingleDbContext context, Tenant tenant,
     bool migrateEvenIfNoDb)
{
    if (!await context.Database.CanConnectAsync())
    {
        //The database doesn't exist
        if (migrateEvenIfNoDb)
            await context.Database.MigrateAsync();
        else
        {
            return $"The database defined by the connection string"+ 
                "'{tenant.ConnectionName}' doesn't exist.";
        }
    }
    else if (!await context.Database
        .GetService<IRelationalDatabaseCreator>()
        .HasTablesAsync())
        //The database exists but needs migrating
        await context.Database.MigrateAsync();

    return null;
}

The migrateEvenIfNoDb parameter is there because EF Core’s Migrate can create a database if you have the authority, e.g. when in development mode and are using a local SQL Server. But if you don’t have authority, e.g. when in production and using Azure SQL Server, then the code must return an error if there isn’t an database.

That’s the end of the code needed to implement the sharding-only approach to multi-tenant applications. The next section shows the extra steps to support the hybrid approach that allows you to use shared-database approach and / or dedicated-database (sharding) approach at the same time.

2. Implement a hybrid multi-tenant application.

The figure below shows what the hybrid design for multi-tenants where each database can either have many tenants in one database (see left and right databases) or only one tenant in a database (see the middle two databases).

NOTE: You don’t have to use both shared-database approach and dedicated-database (sharding). You can use just use sharding if you want.

There is a runnable example of a hybrid multi-tenant in the AuthP repo. Simply clone the repo and set the Example6.MvcWebApp.Sharding project as the startup project. The example assumes you have a locadb SQL Server and seeds the DefaultConnection database with three, non-sharding tenants. The home page shows you how you can move one of the tenants to another database and make it a sharding tenant.

Here is a screenshot just after a non-sharding tenant called “Pets Ltd.” has been moved into its own database and the tenant is now using sharding.

Here are the steps for the hybrid approach, with changes from the sharding approach shown in bold.

  1. Decide on how to manage databases, especially in production
  2. Hold extra information of the tenant and its users in admin database
  3. When a user logs in, then add a ConnectionName and DataKey claim to their claims
  4. Provide a service to convert the user’s ConnectionName claim to a database connection
  5. Provide the connection string and DataKey to the application’s DbContext
  6. Use EF Core’s SetConnectionString method to set the database connection and DataKey
    1. “Turn off” the query filter
    2. Stop setting the DataKey on entites
  7. Migrate the tenant database if not used before
  8. Extra features available in a hybrid design.

1 Decide on how to manage databases, especially in production

Same as sharding-only approach – see this section.

2. Hold extra information of the tenant and its users in admin database

The hybrid approach needs an additional way to handle and shared-database tenants, as they need some form of filter when accessing one tenant out of all the tenants in that database. In the AuthP library the Tenant creates a unique string for each tenant called the DataKey. This DataKey in injected to the tenant application’s DbContext and used in EF Core’s global query filter to only return the data linked to the Tenant. This is explained in the in the article called “Building ASP.NET Core and EF Core multi-tenant apps – Part1: the database”.

In addition, the AuthP Tenant contains a HasOwnDb boolean property, which is true if the tenant is using sharding. This HasOwnDb property is used in a few ways, for instance to remove the query filter on sharding tenants and to return an error if someone tries to add another tenant into a database that has already go a sharding tenant in it – see section 2fi later for more on that.

3. When a user logs in, then add a ConnectionName and DataKey claim to their claims

The hybrid approach needs both the ConnectionName claim and DataKey claim to handle the two types of database arrangement: the ConnectionName is used by every tenant to get the correct database and the DataKey is needed for the shared-database tenants.

However, you have one tenant DbContext to handle both shared-database tenants and sharding tenants and you can’t change the query filter. This means you would be running a query filter on a sharding tenant which doesn’t need it – see section 2e for how I “turn off” the query filter on sharding tenants.

4. Provide a service to convert the user’s ConnectionName claim to a database connection

Same as sharding-only approach – see this section.

5. Provide the connection string and DataKey to the application’s DbContext

For the hybrid approach we need the connection string and DataKey property is added to the Scoped service that uses the IHttpContextAccessor to get the logged-in user (if present) with its claims. The updated service (see GetShardingDataUserNormal class) provides both the ConnectionString and the DataKey to the tenant application’s DbContext.

Obtaining the DataKey is much easier than the connection string because the DataKey was already calculated when the claim was added, so it just about copying the DataKey claim’s Value into a DataKey property in the service.

6. Use EF Core’s SetConnectionString method to set the database connection and DataKey

In a hybrid approach a tenant can be using the shared-database approach or the sharding approach. Therefore, you have to add extra code to every tenant (including sharding) to handle the shared-database DataKey. This extra code includes adding a DataKey property / column to a tenant data classes and the tenant DbContext must have a global query filter configuring on all of the tenant data classes – this is covered in detail in the Part1 article in sections 6 and 7.   

However, we don’t want a sharding tenant to take a performance hit because of the (unnecessary) global query filter, so how do we handle that? The solution is to use the Tenant’s HasOwnDb property to alter the DataKey.

In the AuthP library if the Tenant’s HasOwnDb property is true (and the tenant type is single-level), then the GetTenantDataKey method doesn’t return the normal DataKey, but returns the “NoQueryFilter” string. This allows two things to happen:

6.1. The query filter is “turned off”

The AuthP contains an extension method called SetupSingleTenantShardingQueryFilter which adds a global query filter with a query that can be forced to true if the special DataKey string of “NoQueryFilter” – the code below shows what manual setup of what the code does (NOTE that the recommended automatic approach uses EF Core’s  metadata methods).

modelBuilder.Entity<Invoice>().HasQueryFilter(
    x => DataKey == "NoQueryFilter" || 
    x.DataKey == DataKey);
modelBuilder.Entity<Invoice>().HasIndex(x => x.DataKey);
modelBuilder.Entity<Invoice>().Property(x => DataKey).IsUnicode(false);
modelBuilder.Entity<Invoice>().Property(x => 
    DataKey).HasMaxLength("NoQueryFilter".Length);

The important line is line 2, where the DataKey from the claim is compared with the “NoQueryFilter” string. If the 2 part of the query filter is true, then there is no need to filter on a DataKey . The SQL Server’s execution planner will see that the WHERE clause is always true and will remove WHERE clause from the execution.

NOTE: The AuthP library also supports a hierarchical multi-tenant type (see this article about a hierarchical multi-tenant) and in that case you still need a DataKey to access the various levels in the hierarchical data. Therefore, AuthP won’t turn off the query filer for hierarchical multi-tenant even if you give its own database.

6.2. It stops the setting of the DataKey

The other part of using a DataKey is to set the DataKey in any newly created entity. In a hybrid design if the DataKey is “NoQueryFilter”, then it returns immediately, thus removing the compute time to detect and update the entities that needed a DataKey. See the code below for the updated MarkWithDataKeyIfNeeded method.

public static void MarkWithDataKeyIfNeeded(this DbContext context, string accessKey)
{
    if (accessKey == MultiTenantExtensions.DataKeyNoQueryFilter)
        //Not using query filter so don't take the time to update the 
        return;

    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)
            hasDataKey.DataKey = accessKey;
    }
}

This change doesn’t save much compute time, but still think its worth doing.

7. Migrate the tenant database if not used before

Same as sharding-only approach – see this section.

8. Extra features available in a hybrid design.

To make this work in a real application you need some extra code, for instance how to add new connection string the appsetting file while the application is running. There are also maintenance issues, such as converting a tenant from a shared-database to a dedicated-database tenant (or the other way around) while the application is running.

I will cover these issues and using Azure SQL elastic pools for sharding in a future article.

Conclusion

Multi-tenant applications allow you to serve many tenants from one software source. Having different levels of your service is also a good idea, as it allows your tenants to choose what level of service they want to pay for. And that payment needs to cover your costs for all the cloud service and databases you need to provide your multi-tenant application.

Within the features of your multi-tenant application is its performance, that is the speed (how quickly a query takes) and scalability (Wikipedia defines scalability as the property of a system to handle a growing amount of work by adding resources to the system). The more tenants you have then it takes more work to provide a good performance.

Providing a good performance requires lots of different parts: running multiple instances of the ASP.NET Core applications, upgrading your web server / database server, caching and so on. Sharding is a good approach for handing tenant’s data, but like the other performance improving options it increases the costs.

The client I talked about wanted something working as soon as possible (no surprize there!) so I build a shared-database tenant approach first. But they knew their big tenants would want their own database not just for the performance but for the security. The hybrid handles that and that’s why the AuthP library supports that.

So, if you are building a multi-tenant application for a company you might consider using my open source AuthP library to help you. The library contains all the setup and admin code which there is lot to look through, but that’s because building a multi-tenant application isn’t simple to do. There is a lot of documentation, article and a few videos and if something isn’t clear, then raise an issue and I will try to update the documentation.

Advanced techniques around ASP.NET Core Users and their claims

Last Updated: April 17, 2022 | Created: February 28, 2022

This article describes some advanced techniques around adding or updating claims of users when building ASP.NET Core applications. These advanced techniques are listed below with examples taken from the AuthPermissions.AspNetCore library / repo.

This article is part of the AuthPermissions.AspNetCore (shortened to AuthP in this article) documentation. Others articles about the AuthP library are:

TL;DR; – Summary of this article

  • A logged-in user in an ASP.NET Core application has a HttpContext.User that has information (stored in claims) about the logged-in user and, optionally, data on what pages / features the user can access (known as authorization).
  • The user and their claims are calculated on login and then stored in a Cookie or a JWT Bearer Token. This makes subsequent accessed to the application by that user fast, as the user’s claims are stored and can be read in quickly.
  • You can add extra claims when a user logs in, either to add extra authorization, or value(s) that take a long time to calculate but change infrequently. 
  • The ways you add extra claims on login depends on which of the authentication handlers APIs and which way you store the claims.
  • In certain situations, you might want to recalculate the claims of an all-ready logged in user and this article describes two refresh claims approaches to do this – a periodical approach and an event-driven approach.

Setting the scene – What the AuthP library adds to ASP.NET Core?

The AuthP library provides three main extra authorization features to a ASP.NET Core application. They are:

  • An improved Role authorization system where the features a Role can access can be changed by an admin user (i.e. no need to edit and redeploy your application when a Role changes).
  • Provides features to create a multi-tenant database system, either using one-level tenant or multi-level tenant (hierarchical).
  • Implements a JWT refresh token feature to improve the security of using JWT Token in your application.

The rest of this article describes various ways to add or change the user’s claims in an ASP.NET Core application. Each one either improves the performance of your application or solves an issue around logged-in user claims being out of date.

1. Adding extra claims on to authentication handlers on login

The ASP.NET Core’s authentication handlers are there to ensure that the person who is logging in is verified as a known user. On a successful login it adds information, in the form of claims, about the user and what they can access – known as authorization. Together these form a ClaimsPrincipal class.

This ClaimsPrincipal class is stored in a form that the user to access the application without logging in again. In ASP.NET Core there are two main ways: in a Cookie or in a JWT Bearer Token (shorted to JWT Token). These work in a different way, but they do the same thing – that is provide a secure version of the user’s log-in data that will create a HttpContext.User on every HTTP request the user makes.

The brilliant part of storing the claims in a Cookie or JWT Token is they are super-fast – the claims are calculated on login, which make some time, but for subsequent HTTP requests the claims just read in from the Cookie or JWT Token.

The AuthP library adds extra authorization claims to the ClaimsPrincipal class on login. It does this by intercepting a login event if the claims are stored in Cookie, or for JWT Token it adds code within the building of the Token. AuthP relies on unique string that the authentication handler creates for each user, referred to as the userId. The library has a class called ClaimsCalculator which when called with the user’s userId it returns the extra AuthP claims needed for that user.

Currently, the AuthP library has built-in connectors to individual user accounts and Azure Active Directory (Azure AD) versions when using a Cookie to store the logged-in user’s information. But if you are using a JWT Bearer Token to store the logged-in user’s information then its easy to connect to the AuthP’s claims, which is described later.

ASP.Net Core has lots of authentication handlers and if you want to link into authentication handler’s login you need to know how to tap into their login event. Thankfully, most of the main authentication providers you might want to do use two external services APIs, OAuth2 and OpenIdConnect. The two sections look at how to link into these two APIs to add AuthP claims.

1a. OAuth2: Used by Google, Facebook, Twitter, etc.

The 0Auth2 API is an industry-standard protocol for authorization and is used for lots of external authentication providers and was released in 2012. ASP.NET Core documentation uses OAuth2 for  social logins like Google, Facebook, Twitter, but you can use OpenID Connect for these too (see this article about using OpenID Connect to use Google social login).  

To add extra claims on login, you need to link the OnCreatingTicket event of the ASP.NET Core authentication handler. The event call to your method provides a OAuthCreatingTicketContext parameter which provides the current tokens / claims (at the OAuth2 level they are represented by the AuthenticationToken class). See the code in this section on adding extra tokens /claims on login).

NOTE: that the OAuthCreatingTicketContext contains a HttpContext class, which allows you to use dependency injects to get an instance of the AuthP’s ClaimsCalculator to get the AuthP’s claims, e.g., ctx.HttpContext.RequestServices.GetRequiredService<IClaimsCalculator>();

1b. OpenID Connect:

OpenID Connect is based on OAuth2 and came out in 2014 to fix some issues in OAuth2. Its design is more secure, standardises the authentication steps, and fixes some issues found on OAuth2. ASP.NET Core documentation uses OpenID Connect to use a Microsoft account / Azure AD as a external authentication source.

NOTE: Andrew Lock has written an excellent article about OpenID Connect in ASP.NET Core which explains how OpenID Connect works and how it differs from OAuth2.

As I said earlier, the AuthP library has connector to Azure AD via a method called SetupOpenAzureAdOpenId, which is an excellent example of how to build an OpenID Connect connector. This method links code to OnTokenValidated event that allows you to add / change the logging in user. Just like the OAuth2 event call the parameter contains the HttpContext class, so you can use manual dependency injection to get access to AuthP’s ClaimsCalculator.

1c. Telling AuthP you have set up a custom authentication connector

The last thing you need to do is to add the ManualSetupOfAuthentication method to the registering of the AuthP library in your Program class (or Startup class in NET 3.1). 

2. Adding an extra Claim(s) to a user via AuthP

In the AuthP repo, the Example3 project is a single-level multi-tenant web application called the Invoice Manager. When a companies can sign up to use the Invoice Manage the banner changes to show the company’s name. Styling the application to customer like that improves the user’s experience, but the downside that every HTTP request has a database access to get the company’s name twice (one for redirecting to the correct page and another to add the header). That hits the performance of the overall application so, how could I improve that?

As already explained, claims are calculated when a user logs in and then stored in a Cookie or a JWT Bearer Token. This means that if I added the company’s name as a claim, then I will remove two database queries for every HTTP request.

Version 2.3.0 of the AuthP library has a RegisterAddClaimToUser<TClaimsAdder> method that will allow you to extra claims when a user logs in. The class you register with this method must implement the IClaimsAdder interface and it then registered with the ASP.NET Core dependency injection. When the AuthP’s ClaimsCalculator is called it will add the default AuthP’s default claims and all of the claims you added via the RegisterAddClaimToUser method.

The code below shows the code I used to find the tenant company name. NOTE: if the user isn’t a tenant user, then no claim is added.

public class AddTenantNameClaim : IClaimsAdder
{
    public const string TenantNameClaimType = "TenantName";

    private readonly IAuthUsersAdminService _userAdmin;

    public AddTenantNameClaim(IAuthUsersAdminService userAdmin)
    {
        _userAdmin = userAdmin;
    }

    public async Task<Claim> AddClaimToUserAsync(string userId)
    {
        var user = (await _userAdmin.FindAuthUserByUserIdAsync(userId)).Result;

        return user?.UserTenant?.TenantFullName == null
            ? null
            : new Claim(TenantNameClaimType, user.UserTenant.TenantFullName);
    }
}

A simple test of displaying the company home page many times gave me the following timings:

  1. Before adding the tenantName claim: 20 to 25 ms
  2. After adding the tenantName claim: 19 to 20 ms

That’s a ~3 ms / ~10% performance improvement for a small amount of extra code, so I think that’s a win. But what happens if the tenant’s (company’s) is changed? Read the next two approaches for ways to overcome this.

NOTE: You can see this approach in the Example3 ASP.NET Core project. If your clone the  AuthPermissions.AspNetCore repo and run the Example3.MvcWebApp project then it will seed the database with demo data on its first run and you can try this out.

3. Periodically refreshing the logged-in user’s claims

There are a few times where your want the user’s claims to be updated. The main one is if you change some of the user’s authorization parts, like Roles, or you change the Permissions in an AuthP Role, then these only get changed when the user logs out and logs back in again. This can have security issues, as you might have a logged-in users and you want their authorization revoked in some way.

My answer to this issue is to recalculate the user’s claims every so often – how often you update the claims of a logged-in user is a compromise between performance and claims being ‘correct’. If you recalculate every second, then for applications with lots of users will become slow, as recalculating the AuthP’s claims needs two database accesses to set up the Permissions and the DataKey. Alternatively, updating the company name isn’t a security issue, so you might want to wait say 10 minutes or more between recalculating the claims.

How you do this depends on whether you are using a Cookie or a JWT Bearer Token to hold the calculated user’s claims. The two subsections show you how to do this:

3a. Refreshing the claims in a Cookie

Refreshing a user’s authentication Cookie is available via the cookie OnValidatePrincipal event. Lots of authentication handlers use an authentication Cookies by default, but its sometimes its hard to find the Cookie events. The individual user accounts authentication handler is easy to set up – see the code below as to you configure, with the setup of the event highlighted.

services.AddDefaultIdentity<IdentityUser>(options =>
        options.SignIn.RequireConfirmedAccount = false)
    .AddEntityFrameworkStores<ApplicationDbContext>();
services.ConfigureApplicationCookie(options =>
{
    options.Events.OnValidatePrincipal = 
        PeriodicCookieEvent.PeriodicRefreshUsersClaims;
});

Finding the way to link cookie events when using OpenID Context to use an external Azure AD was much harder find, but the code below shows how to set up the PeriodicRefreshUsersClaims method.

    .AddMicrosoftIdentityWebApp(identityOptions =>
    {
        var section = _configuration.GetSection("AzureAd");
        identityOptions.Instance = section["Instance"];
        identityOptions.TenantId = section["TenantId"];
        identityOptions.ClientId = section["ClientId"];
        identityOptions.CallbackPath = section["CallbackPath"];
        identityOptions.ClientSecret = section["ClientSecret"];
    }, cookieOptions =>
        cookieOptions.Events.OnValidatePrincipal =
            PeriodicCookieEvent.PeriodicRefreshUsersClaims);

You also need to create a claim holding the time when the user should be updated. Here is some code that adds a claim called TimeToRefreshUserClaim, which contains a time one minute in the future.

public class AddRefreshEveryMinuteClaim : IClaimsAdder
{
    public Task<Claim> AddClaimToUserAsync(string userId)
    {
        var claimValue = DateTime.UtcNow.AddMinutes(1).ToString("O");;
        var claim = new Claim(TimeToRefreshUserClaimType, claimValue)
        return Task.FromResult(claim);
    }
}

This claim will be used by the method linked to the Cookie’s OnValidatePrincipal event in the PeriodicCookieEvent class, which is shown below. The highlighted lines compares the time in the TimeToRefreshUserClaim claim with the current time (UTC) and updates the user’s claims (including the TimeToRefreshUserClaim) if the user’s time is older than DateTime.UtcNow.

NOTE: I’m recommend a refresh of 1 minute. I only used short time as it’s easier to check it’s working.

public static async Task PeriodicRefreshUsersClaims
    (CookieValidatePrincipalContext context)
{
    var originalClaims = context.Principal.Claims.ToList();

    if (originalClaims.GetClaimDateTimeUtcValue
        (TimeToRefreshUserClaimType) < DateTime.UtcNow)
    {
        //Need to refresh the user's claims 
        var userId = originalClaims.GetUserIdFromClaims();
        if (userId == null)
            //this shouldn't happen, but best to return
            return;

        var claimsCalculator = context.HttpContext.RequestServices
            .GetRequiredService<IClaimsCalculator>();
        var newClaims = await claimsCalculator
              .GetClaimsForAuthUserAsync(userId);
        newClaims.AddRange(originalClaims
            .RemoveUpdatedClaimsFromOriginalClaims(newClaims));

        var identity = new ClaimsIdentity(newClaims, "Cookie");
        var newPrincipal = new ClaimsPrincipal(identity);
        context.ReplacePrincipal(newPrincipal);
        context.ShouldRenew = true;
    }
}
private static IEnumerable<Claim> RemoveUpdatedClaimsFromOriginalClaims
    (this List<Claim> originalClaims, List<Claim> newClaims)
{
    var newClaimTypes = newClaims.Select(x => x.Type);
    return originalClaims.Where(x => !newClaimTypes.Contains(x.Type));
}

This method is very quick if the user doesn’t need refreshing – it calls DateTime.Parse of a string in an already loaded claim and compare to a time which takes a few microseconds. That’s important because it will be called on every HTTP request. If the claims do need refreshing there are a few database accesses which takes milliseconds, which is why there is a compromise between performance and claims being ‘correct’.

NOTE: The line context.ShouldRenew = true at the end of the PeriodicRefreshUsersClaims method. This makes sure that the Cookie is updated. If you don’t add this line, then new claims work in this HTTP request, but the claims aren’t changed for the next HTTP request.

NOTE: You can see this approach in the Example3 ASP.NET Core project. If your clone the  AuthPermissions.AspNetCore repo and run the Example3.MvcWebApp project then it will seed the database with demo data on its first run and you can try this out.

3b. Refreshing the claims in a JWT Bearer Token

The basic JWT Bearer Token (shortened to JWT Token) can’t be updated as once it is created you can’t change it until it times out – maybe 8 hours later. But this long life of the JWT Token creates a security issue as of a hacker can get a copy of the JWT Token they can assess the application too. Also, there is logout of an JWT Token which is another security issue too.

The solution to these JWT Token security issues is adding a refresh token and the AuthP library contains an implementation of the refresh token in ASP.NET Core. And this implementation can refresh the user’s claims, but first let’s look at how the refresh token works.

The diagram below shows that the JWT Token times out quickly, in this case every five minutes, but the refresh token provides the authority to create a new JWT Token.

In this scheme the JWT Token can still be copied but its only valid for a small time. The refresh token also provides extra security because it can only be used once, and it can be revoked which will log out the user. The short life JWT Token time and the one-time use of the refresh token makes the use of a JWT Token much more secure. It also provides a way to periodically update the user’s claims.

The AuthP library provides a TokenBuilder class that can create just a JWT Token or a JWT Token with an associated refresh token. When creating the JWT Token it uses AuthP’s ClaimsCalculator class to add the AuthP’s claims. This means if you are using the JWT Token with refresh, then the user’s claims are updated on every refresh.

NOTE: For more information on JWT Token with refresh you can look at a video I created explaining how the JWT Token with refresh works,  or look at the AuthP’s JWT Token refresh explained documentation.

4. Refreshing the logged-in user’s claims on an event

The next challenge is when we need to immediately refresh the claims in a logged-in user in a way that doesn’t make every HTTP slow. This “immediately refresh” requirement is needed when using the hierarchical multi-tenant “Move Tenant” feature. This feature changes the DataKey of the moved tenants, which means the DataKey in users linked to a tenant that was moved need an immediate updated of their DataKey claim.

Implementing the immediately refresh the claims in a logged-in user requires three parts:

  1. A way to know if a logged-in user’s claims need updating.
  2. A way to tell ASP.NET Core to immediately refresh logged-in user’s DataKey claim
  3. A way to detect when a DataKey is changed

NOTE: This only works with Cookie authentication because you can’t force an immediate update a JWT token.

NOTE: You can see this approach in the Example4 ASP.NET Core project. Make sure to look at the RefreshUsersClaims folder in the Example4.ShopCode project for the various code shown in this approach.

4a. A way to know if a logged-in user’s claims need updating.

In the periodic refresh of the logged-in user’s claims a claim contained the time when it should be updated. With the refresh on an event, we need the opposite –the time when the logged-in user’s claims were last updated. The code below adds a claim that contains the time the claims were last created / updated.

public class AddGlobalChangeTimeClaim : IClaimsAdder
{
    public Task<Claim> AddClaimToUserAsync(string userId)
    {
        var claimValue = DateTime.UtcNow.ToString("O");
        var claim = new Claim(EntityChangeClaimType, claimValue)
        return Task.FromResult(claim);
    }
}

4b. Telling ASP.NET Core to immediately refresh user’s claims

We use the same cookie OnValidatePrincipal event that the periodic update of claims, but now we need the last time a DataKey was changed. If you only have one instance of your application you could use a static variable, but if you have multiple instances of your application running (Azure calls this Scale Out), then a static variable won’t work – you need a global resource that all the instances can see.

For the “multiple instances” case you could use the database, but that would need a database access on every HTTP request, which would hit performance. An in-memory distributed cache like Redis would be quicker, but I used a simpler global resource – the application’s FileStore. The GlobalFileStoreManager class in the AuthP repo implements a global FileStore, storing the value in a text file stored in the ASP.NET Core wwwRoot directory. The GlobalFileStoreManager is quicker than a database, only taking 0.02 ms. (on my dev machine) to read.

So, the OnValidatePrincipal event can use the GlobalFileStoreManager to read the last time a DataKey was updated, and it can compare that to the last time the logged-in user’s claims and update the claims if they are older.

The code below shows the event method in the TenantChangeCookieEvent class. This is almost the same as the periodic refresh event, with the changed lines highlighted.

public static async Task UpdateIfGlobalTimeChangedAsync
    (CookieValidatePrincipalContext context)
{
    var originalClaims = context.Principal.Claims.ToList();
    var globalTimeService = context.HttpContext.RequestServices
        .GetRequiredService<IGlobalChangeTimeService>();
    var lastUpdateUtc = globalTimeService.GetGlobalChangeTimeUtc();

    if (originalClaims.GetClaimDateTimeUtcValue
           (EntityChangeClaimType) < lastUpdateUtc)
    {
        //Need to refresh the user's claims 
        var userId = originalClaims.GetUserIdFromClaims();
        if (userId == null)
            return;

        var claimsCalculator = context.HttpContext.RequestServices
            .GetRequiredService<IClaimsCalculator>();
        var newClaims = await claimsCalculator
            .GetClaimsForAuthUserAsync(userId);
        newClaims.AddRange(originalClaims
            .RemoveUpdatedClaimsFromOriginalClaims(newClaims)); 

        var identity = new ClaimsIdentity(newClaims, "Cookie");
        var newPrincipal = new ClaimsPrincipal(identity);
        context.ReplacePrincipal(newPrincipal);
        context.ShouldRenew = true;
    }
}

4a. A way to detect when a DataKey is changed

The version 2.3.0 of the AuthP library introduces an IRegisterStateChangeEvent interface, which allows you to use EF Core 5’s events to detect a series of events. This lets you trigger code when different events happen within the AuthP’s DbContext.

The code below shows the code to register the specific event(s) you want to use. In this case I want to detect a change to the ParentDataKey property in the Tenant entity class, so we add a private event hander called RegisterDataKeyChange to the ChangeTracker.StateChanged event. Once that happens it uses a service, which in turn uses the GlobalFileStoreManager to set the global

public class RegisterTenantDataKeyChangeService 
    : IRegisterStateChangeEvent
{
    private readonly IGlobalChangeTimeService _globalAccessor;
    public RegisterTenantDataKeyChangeService(
        IGlobalChangeTimeService globalAccessor)
    {
        _globalAccessor = globalAccessor;
    }

    public void RegisterEventHandlers(AuthPermissionsDbContext context)
    {
        context.ChangeTracker.StateChanged += 
            RegisterDataKeyChange;
    }

    private void RegisterDataKeyChange(object sender, 
        EntityStateChangedEventArgs e)
    {
        if (e.Entry.Entity is Tenant
            && e.NewState == EntityState.Modified
            && e.Entry.OriginalValues[nameof(Tenant.ParentDataKey)] !=
            e.Entry.CurrentValues[nameof(Tenant.ParentDataKey)])
        {
            //A tenant DataKey updated, so set the global value 
            _globalAccessor.SetGlobalChangeTimeToNowUtc();
        }
    }
} 

This is very efficient because it’s only a DataKey change that causes the immediate update of the logged-in user’s claims.

NOTE: There is a very small possibility that a logged-in user could start a database access before the DataKey is changed, and database access is delayed by the “Move Tenant” transaction. In this case the user’s database access will use the old DataKey. If you want to ensure this cannot happen you could put your application into a mode where the user is diverted to a “please wait” page for a few seconds prior to the “Move Tenant”. I have an old article about how make your application “down for maintenance” for ASP.NET MVC5, but the same approach would work with ASP.NET Core

Conclusion

Most ASP.NET Core authentication handlers provide the basic claims but little or no authorization claims. A few, like individual user accounts with Roles-based authorization provide a full  authentication / authorization solution. The AuthP library adds authorization claims similar to the Roles-based authorization for accessing features / pages and also the database (i.e. multi-tenant applications).

As you have learnt in this article claims are the key part of authentication and authorization. They are also a fast, because they are calculated on login, but every HTTP request after that the user’s claims are read in from a Cookie or a JWT Token. If you have a value that takes time to create and use in ever HTTP request, then consider turning that value into a claim on login.

The downside of the claims being stored in a Cookie or a JWT Token is that is fixed throughout the time the user is logged in. In a few cases this is a problem, and this article gives two ways to overcome this problem while not causing a slowdown of your application.

Most of the techniques in this article are advanced, but each one has a valid use in real-world applications. You most likely won’t use these techniques very often, but if you need something like this then you now have some example code to use.

Happy coding.

Building ASP.NET Core and EF Core hierarchical multi-tenant apps

Last Updated: April 17, 2022 | Created: January 31, 2022

This article looks at a specific type of multi-tenant where a tenant can have a sub-tenant. For instance, a large company called XYZ with different businesses could have a top-level tenant called XYZ, with sub-tenants for each business it has. This multi-tenant type is known as hierarchical multi-tenant application.

The big advantage of a hierarchical multi-tenant applications is that higher level tenant can see all the data in the lower levels. For instance, the top-level XYZ tenant could see all the data in the businesses below, but each individual business can’t see the data of the other businesses – the “Setting the scene” section gives more examples of where a hierarchical multi-tenant approach can be useful.

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
  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 (this article)
  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

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 describes what a hierarchical multi-tenant application is and provides a couple of examples of where a hierarchical approach can help.
  • The article lists the three changes to the single level multi-tenant setup listed in the Part 1 article to create a hierarchical multi-tenant application.
  • Because a hierarchical multi-tenant application has sub-tenants, then the name and DataKey of a tenant are a combination of the names / DataKeys of the parent tenants.
  • Many of the features / administration of a hierarchical multi-tenant are the same as a single level multi-tenant application, but the create and update of a tenant requires a parent tenant to define the tenant’s place in the hierarchy. There is also an extra feature which allows you to move a tenant to a different place / level in the hierarchy.

Setting the scene – when are hierarchical multi-tenant applications useful?

A hierarchical multi-tenant is useful when the data, or users, are managed in sections. Typically, the sub-tenants are created a business grouping or geographic areas, or both. Each sub-tenant is separate from each other, and a user linked to a sub-tenant can only see the data in their tenant. While a user linked to a top-level tenant can all the sub-tenant’s data. Here is a real example to make this more real.

I was asked to design a hierarchical multi-tenant for a company that manages the stocking and sales for many chains of retail outlets. Companies would sign up their service, and some of the companies had hundreds of outlets all across the USA and beyond, which were managed locally. This meant the multi-tenant had to handle multiple layers and the diagram below gives you an idea of what that might look

Using the diagram above, the hierarchical multi-tenant design allows:

  • The “4U Inc.” tenant to see all the data from all their shops worldwide
  • The “West Coast” tenant to only see the shops in their West Coast region
  • The “San Fran” tenant to see the shops in their San Fran region
  • Each retail outlet can only see their data.

The data contains stock and sales data, including information on the person who made the sale. This allows business analytics, restock scheduling and even the performance of shops and their staff across the different hierarchical levels.  

So, to answer the question “when are hierarchical multi-tenant applications useful?” it’s when there are multiple groups of data and users, but there is a business advantage for a management team to have access to these multiple groups of data. If you have a business case like that, then you should consider a hierarchical multi-tenant approach.

How AuthP library manages a hierarchical multi-tenant

The part 1 article, which is about setting up the multi-tenant database, gave you eight stages (see this section) to register the AuthP library and setting up the code to split the data into a normal (single-level) tenants. The splitting up the data into tenants uses a string, known as the DataKey, that contains the primary key (e.g. “123.”) of the tenant and EF Core’s global query filter uses an exact match, to filter each tenant e.g.

modelBuilder.Entity<YourEntity>().HasQueryFilter(
   entity => entity.DataKey == dataKey;

The big change when using a hierarchical multi-tenant is that the AuthP creates the Datakey with a combination of the tenant primary keys. Then, by changing the EF Core’s global query filter to a StartWith filter (see below), then the data can be managed as a hierarchical multi-tenant

modelBuilder.Entity<YourEntity>().HasQueryFilter(
   entity => entity.DataKey.StartsWith(dataKey);

So, the hierarchical multi-tenant the AuthP creates the Datakey by combining the DataKeys of the higher levels, so “4U Inc.” might be “1.”, while “4U Inc., West Coast” might be “1.3.” and so on. The diagram shows the layers again, but now with the DataKeys added. From this you can see a DataKey of 1.3.7. would access the two shops in LA, but the people within each shop can only see the stock / sales for their shop.

This simple change to the EF Core’s global query filter in your application (and some extra AuthP code) changes your application from being a normal (single level) multi-tenant to a hierarchical multi-tenant.

The next section gives you changes to the eight steps to set up your database shown in the part 1 article.

Setting up a hierarchical multi-tenant application

It turns out the setting up of a hierarchical multi-tenant application is almost the same as setting up a single-level multi-tenant application. The Part 1 article covers the setting up the database for a single-level multi-tenant and a hierarchical multi-tenant only changes three of those steps: 1, 6, and addition to step 8.

You can see the full list of all the eight steps in Part 1 so I have listed just the changed steps, but you MUST apply all the steps in the Part 1 article – it’s just the following steps are different.

1. Register the AuthP library in ASP.NET Core

You need to add the AuthP NuGet package to your application and set up your Permissions (see this section in the Part 1 article). Registering AuthP to the ASP.NET Core dependency injection (DI) provider is also similar, but there is one key difference – the setting of the TenantType in the options.

The code below is taken from the Example4 project (with some test data removed) in the AuthP with the TenantType setup highlighted.

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

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

NOTE: Look at the documentation about the AuthP startup setting / methods for more information and also have a look at the Example4 ASP.NET Core project.

6. Add EF Core’s query filter

This stage is exactly the same as in the step 6 in the Part 1 article, apart from one change – the method you call to set up the global query filter, which is highlighted in the code.

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

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

        //other code removed…

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

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

This code automates the setting up of the EF Core’s query filter, and its database type, size and index. Automating the query filter 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 AddHierarchicalTenantReadOnlyQueryFilter extension method is part of the AuthP library and does the following:

  • Adds query filter to the DataKey in an entity which must start with the DataKey provided by the service IGetDataKeyFromUser described in step 3.
  • Sets the string type to varchar(250). That makes a (small) improvement to performance.
  • Adds an SQL index to the DataKey column to improve performance.

NOTE: a size of 250 characters for the DataKey allows a depth of 25 sub-tenants, which should be enough for most needs.

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

A hierarchical multi-tenant application stills needs to sync changes between the AuthP database and your application’s database, which means creating a ITenantChangeService service and register that service via the AuthP library registration. I’m not going to detail those steps because they are already  described in stage 8 in the Part 1 article.

What is different is the that a tenant’s rename and delete are much more complicated because a there may be multiple tenants to update or delete. The AuthP code manages this for you, but you need to understand that it will your ITenantChangeService code multiple times, and the some of the calls will include higher layers.

Also, you must implement the MoveHierarchicalTenantDataAsync method in your ITenantChangeService code, because AuthP allows you to move a tenant, with any sub-tenants, to another place in the hierarchical tenants – see next section for a diagram of what a move can do.

NOTE: I recommend you look at Example4’s RetailTenantChangeService class for a an example of how you would build your ITenantChangeService code for hierarchical tenants.

AuthP’s tenant admin service

The AuthP’s ITenantAdminService handles both a normal (single-level) and a hierarchical multi-tenant. The big change is a tenant can have a sup-tenants, so a list of tenants shows the full tenant’s name, which is a combination of all the tenant names with a | between them – see the screenshot from Example4 for an example of hierarchical tenants.

Note that hierarchical tenants have an extra feature called Move that the normal (single level) multi-tenant doesn’t have. This allows you to move a tenant, with all of its sub-tenants, to another level in your tenants. The diagram below shows an example of a move, where a new “California” tenant is added and then the “LA” and “SanFran” tenants are moved into the “California” tenant. The Move code recursively goes through the tenant + sub-tenants updating the parent and recalculating the DataKey for each tenant. This also calls your ITenantChangeService code at each stage so that you can update the DataKey of your application’s data.

Other general multi-tenant features such as administration features (see the part 2 article) and versioning (see the part 3 article) also apply to a hierarchical multi-tenant approach.

Conclusion

When I was asked to design and build a hierarchical multi-tenant application for a client, I found the project a challenge and I really enjoyed working on it. Over the years I have thought about the client’s project, and I have found ways to improve the first design.

Two years after the client project I wrote an article called “A better way to handle authorization in ASP.NET Core” which improved on some the client project. Another two years later I came up with the AuthP library improves the code again and makes it much easier for a developer to create a hierarchical multi-tenant (and other things too).

My first-hand experience of designing and building a real hierarchical multi-tenant application for a client means I had a good idea on what is really needed to create a proper application. For instance, the part 2 article has a lot of administration features which I know are needed, and the part 3 article adds an important feature of offering different versions of your multi-tenant application to users.

Happy coding.

Multi-tenant apps with different versions can increase your profits

Last Updated: April 17, 2022 | Created: January 18, 2022

This article explores ways you can make each tenant in your multi-tenant applications have different features – for instance, Visual Studio has three versions: Community, Pro, and Enterprise. This is often called versioning, and has four benefits:

  • Having different versions / prices will increase your potential user base.
  • The users that need the application’s advanced feature will pay more.
  • Having different versions makes your really useful features stand out.
  • People are more likely to try your service if there is a free version to try.

In this article I show how the features in the AuthPermissions.AspNetCore library (shortened to AuthP in these articles) allows to add versioning to your multi-tenant application. 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
  2. Administration: different ways to add and control tenants and users
  3. Versioning your app: Creating different versions to maximise your profits (this article)
  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

TL;DR; – Summary of this article

  • The AuthP version 2 release allows you to create different versions of your multi-tenant application and you can now add extra Roles to tenant depending on what the customer selected and paid for – this it known as versioning.
  • The AuthP version 2 release also a Role filter to hide Roles that allow access to advanced feature from tenant users, which means a tenant user can safely manage users in their tenant.
  • The AuthP repo contains an ASP.NET Core MVC multi-tenant application which can be found in the Example3 project. This application is called the “Invoice Manage” and has three versions: Free, Pro, and Enterprise. This provides a runnable example of how to build a version a multi-tenant application using AuthP.
  • The article covers
    • How it hides advanced feature Roles from tenant users so that a tenant person can take over the job of managing the users in their tenant.
    • The three types of Roles that let you to version different types of multi-tenant application.
    • How to add the versioning Roles to your application.
    • How to add Roles to a tenant to turn on different versions of tenants.
    • How the admin user inside a tenant can get the correct roles for their tenant.
    • How your admin staff can manually add Roles to a tenant.

Introduction to AuthP’s v2 improved Roles

In the AuthP library controls what a logged-in user can access via Roles and Permissions (read the original AuthP article to learn about Roles and Permissions). In version 1 of the AuthP library all the tenants could access the same set of Roles. This meant you a) couldn’t have different versions of a tenant and b) all the administration of AuthP users had to be done by top-level users of your multi-tenant application – referred to as app admin users.

The Part 2 article, which covers administration, I suggested that having an admin user in a tenant level (referred to as tenant admin users) is useful to reduce the work your admin staff has to do (I also think the tenants would like that as they can change things immediately). This is only possible because of an improvement in version 2 of the AuthP library which adding a RoleType to the Roles. The purpose of adding the RoleType was to:

  1. Allows a Role contains access to advanced features, such as deleting a tenant, to be hidden from tenant users. This allows a tenant admin user can manage their user’s Roles safely, as the advanced admin Roles aren’t available to a tenant admin user.
  2. Add two RoleTypes that are registered to a Tenant instead of directly to a AuthP user – these are known as Tenant Roles. This allows you to add extra Roles to a tenant, which gives the users in that tenant have extra features over the base (normal) Roles. This is the key to creating different versions of your application.

NOTE: There is also a default RoleType, known as a normal RoleType, which any user can have. These Roles define the basic Roles that everyone can use.

The next sections describe how these two changes helps when building multi-tenant applications.

  1. Hiding Roles that contains access to advanced features.  This describes how a tenant admin can safely manage their users’ Roles, because Roles that a tenant user aren’t allowed to have are filtered out.
  2. Versioning your application. This describes how each tenant can have its own Roles, called tenant Roles.  These tenant Roles allow you to add extra features in a tenant, thus creating the versioning of your application.

1. Hiding Roles that contains access to advanced features

The AuthP library controls access to your applications using features via Roles and Permissions, but some of the advanced features, such a deleting a tenant, should only accessible to your admin staff. The Permissions that control access to these powerful features are called advanced Permissions (see this section in the Permission document about advanced Permissions).

When a Role is created or updated the Permissions in the Role are scanned, and if any of the Permissions are marked as advanced Permissions, then the Role’s RoleType will be set to “HiddenFromTenants”. You can also set the RoleType manually (shown later in this article), but if advanced Permissions are found, then it will always set to “HiddenFromTenants”.

This change allows a tenant admin user to manage the Roles that their users have, as the Roles containing access to advanced (dangerous) features aren’t available to the tenant users. This feature is fundamental to allowing a tenant admin user to manage their users.

2. Versioning your application

As I said at the start, versioning your multi-tenant application can increase your potential user base and your profits. The multi-tenant application I designed for a client required different versions to cover the different features their retail customers needed, and of course the versions with more features had a higher price.

An analyse of the versioning needs comes up with three types of Roles to add versioning:

  • Base Roles (also known as normal Role): These are Roles that every valid user can access, but a non-logged in user shouldn’t be able to access. This is the base version of your application.
  • Tenant auto-add Roles: These are extra Roles in the higher versions of a tenant. If an auto-add Role is turned on in a tenant, then all logged-in users in that tenant can access the auto-add Role. In the example later I use an auto-add Role to provide extra analytics Role to the “Invoice Manager” application.
  • Tenant admin-add Roles: These are extra Roles in the higher versions of a tenant. If an admin-add Role is turned on, its available for users in the tenant, but it’s not automatically added to every user in the tenant. This is useful in more complex applications where users within a tenant have different access. In the example later I use an admin-add Role to promote a user to be the tenant admin in the “Invoice Manager” application, which allows that user to invite new users to the tenant.

These three types of Roles allow you to build different versions into your multi-tenant applications. For SaaS (Software as a Service) applications your versions can offer customers different versions / prices for users to choose from. While a multi-tenant application for big multi-national company can not only separate the data for each division in a company, but also what features users in a division can access.

The next section describes an example multi-tenant application called the “Invoice Manager” which uses the three types of Roles defined to create an application with three versions.   

Building the multi-tenant “Invoice Manager” application

The rest of the article show how I built a versioned multi-tenant application that I call the “Invoice Manager”. The Invoice Manager application is a multi-tenant ASP.NET Core MVC application and can be found in the Example3 project in the AuthP repo. It has three versions:

  • Free: This version only allows one user in the tenant. This works for one-person companies, but also acts as a trial system that larger companies can try out the Invoice Manager’s features.
  • Pro: This allows multiple users within the tenant, which works for companies with many employees. This uses a tenant admin-add Role to promote the person that signed up for the Pro (or Enterprise) version to a tenant admin, which allows that user to invite new user to join their tenant.
  • Enterprise: This gives access to an invoice analyse feature to all the users in this tenant. This uses a tenant auto-add Role so that all the users in an Enterprise version tenant have access to the invoice analytical features. Companies that want this feature will pay extra to get this feature.

The Invoice Manager application allows a customer to sign up for this service where the customer picks what version they want / can afford. You have seen some of Invoice Manager features in the Part 2, administration article, but I didn’t cover how I managed the Roles and the versions. This article focuses on code / Roles that make the versioning work. The parts covered are:

  1. Adding the Role types needed for the three versions
  2. Creating a tenant with the Roles defined by the user’s selected version
  3. Allowing the tenant admin user to change a user’s Roles
  4. Manually adding Roles to a tenant

2a. Adding the Role types needed for the three versions

In this simple example I only need three Roles, one of each type.

  • Tenant user: This Role allows a user to access the Invoice Manager’s features, e.g. listing and adding invoices. This holds the base set of Invoice Manager’s features and is a Normal RoleType, and every valid tenant user should have this Role.
  • Tenant admin: This Role allows the user to a) sent invites to users to join their tenant, and b) can alter the Roles of users in the admin tenant’s tenant. This Role’s RoleType is admin-add, which it has be added to a user manually or by your sign-up code and is applied to Pro or Enterprise tenants, and not on the Free version.
  • Enterprise: This Role unlocks an analytic feature it the Invoice Manager code.  This Role’s RoleType is auto-add, which means the Role will automatically applied to every user within an Enterprise tenant.

The table below summaries the Roles in each version of the tenants.

TYPEFreeProEnterpriseWhat
NormalTenant user (everyone has)Tenant user (everyone has)Tenant user (everyone has)Gives access to basic features
Admin-add Tenant admin (given to a user)Tenant admin (given to a user)Can upgrade a user to Tenant admin
Auto-add  Enterprise (all users get this)Gives every user in tenant to this feature(s)

To create these Roles you need to be an app admin (see definition of an app admin and tenant admin in the part 2 article) and you manually set up each Role using AuthP’s AuthRoleAdminService’s  CreateRoleToPermissionsAsync method. The screenshot below comes from the Example3 ASP.NET Core MVC application and allows you to manually create a Role and set the RoleType.

The tenant Roles (tenant auto-add and tenant admin-add) can be added to a tenant. The Normal Role can be added to any AuthP user, while the HiddenFromTenant RoleType can only be added to non-tenant users. The AuthP’s library checks these rules and will give you helpful errors if you try to assign a Role in the wrong place.

2b. Creating a tenant with the user’s selected version

If you have read the Part 2, administration article, then you will have seen a section called Take over the registering of a new user, which sends a new user to “Sign up now!” page to pick which version of the Invoice Manager they want. The Part 2 section was focusing on the registration of the user, but in this article, we are focusing on the different Roles that are applied to the tenant and the user.

Here is a screenshot of the “Sign up now!” page to remind you what the sign up page offers.

NOTE: Please do try this example. Clone the AuthP repo and run the Example3 ASP.NET Core project. On the home page there is a “Sign up now!” tab in the NavBar with will take you to the “Welcome to the Invoice Manager” page where you can select which version you want.

I have split up the “Sign up now!” code into sections to show the parts about Roles and versioning. The first part are the two dictionaries using the enum TenantVersionTypes (Free, Pro, Enterprise) that hold the users Roles and the Tenant Role. Using a dictionary makes it very clear what Roles go where, any its easy to change / expand in the future.

NOTE: You can find “Sign up now!” code in the AddUserAndNewTenantAsync method inside UserRegisterInviteService class.

private readonly Dictionary<TenantVersionTypes, List<string>> 
     _rolesToAddUserForVersions = new()
{
    { TenantVersionTypes.Free, new List<string> { "Tenant User" } },
    { TenantVersionTypes.Pro, new List<string> { "Tenant User", "Tenant Admin" } },
    { TenantVersionTypes.Enterprise, new List<string> { "Tenant User", "Tenant Admin" } }
};

private readonly Dictionary<TenantVersionTypes, List<string>> 
    _rolesToAddTenantForVersion = new()
{
    { TenantVersionTypes.Free, null },
    { TenantVersionTypes.Pro, new List<string> { "Tenant Admin" } },
    { TenantVersionTypes.Enterprise, new List<string> { "Tenant Admin", "Enterprise" } },
};

Notice that:

  • Lines 1 to 7: This defines what Normal RoleTypes are added to the user setting up the tenant. In the Free version there is no “Tenant Admin” Role
  • Lines 9 to 15: This defines what tenant Roles (Admin-add and Auto-add) to the tenant. This adds extra Roles that the users can / will use.

The actual code starts with some checks (check version is set and requested tenant name is available, etc.) which I left out to make the main part easier to read. Notice the references to a directory to get the Roles for the Tenant and the AuthP user.

//Add a new user, in this case using individual users account
var userStatus = await GetIndividualAccountUserAndCheckNotAuthUser(
    dto.Email, dto.Password);
if (status.CombineStatuses(userStatus).HasErrors)
    return status;

//Now we can create the tenant, with the correct tenant roles
var tenantStatus = await _tenantAdminService.AddSingleTenantAsync(
    dto.TenantName, _rolesToAddTenantForVersion[tenantVersion])
if (status.CombineStatuses(tenantStatus).HasErrors)
    return status;

//This creates a user, with the roles suitable for the version of the app
status.CombineStatuses(await _authUsersAdmin.AddNewUserAsync(
    userStatus.Result.Id, dto.Email, null,
    _rolesToAddUserForVersions[dto.GetTenantVersionType()], 
   dto.TenantName));

Notice that:

  • Lines 8 to 11: This creates the tenant, with the correct tenant Roles for the user’s selected version.
  • Lines 14 to 17: This creates the AuthP user, with the Normal Roles defined for the user’s selected version.

The result is a customer can sign-up to the Invoice Manager version they need / can pay for and the code in the Invoice Manager uses an ASP.NET Core authentication provider to register the new user and then uses the AuthP’s admin services to set up the Tenant and AuthP’s user with the correct Roles for the version the customer has selected.

2c. Allowing the tenant admin user to change a user’s Roles

In the Pro and Enterprise versions the user that signed up to the Invoice Manager service it promoted to being a tenant admin. This allows this user to:

What the Part 2 article didn’t cover what is going on inside, especially when the tenant admin changes the Roles of a user in their tenant. In this case there are two types of Roles that the tenant admin can change:

  • The normal (base) Roles that anyone can have
  • The tenant admin-add Roles that are in the Tenant’s list of Roles.

NOTE: The tenant auto-add RoleTypes assigned to a tenant are automatically added to every logged-in user in that tenant.

When a tenant admin is editing a user’s Roles the page must list the normal and tenant admin-add Roles from the user’s Tenant. The AuthP user admin service contains a method called GetRoleNamesForUsersAsync(string userId) which looks for the two types of Roles to create a dropdown list of Roles that the user can have. This method takes the userId of the user being updated, so that it can add any admin-add Roles in the user’s tenant.

The screenshot below shows the user user1@4uInc.com who in the tenant called “4U Inc.”, who’s version is Enterprise.  The dropdown list of Roles shows the normal Role called “Tenant User” and a tenant admin-add Role called “Tenant Admin” Role. Only the “Tenant User” is currently selected, but if the tenant admin selected the “Tenant Admin” Role too, then the user1@4uInc.com would also be an tenant admin too.

4. Manually adding Roles to a tenant

In the “creating a tenant with the user’s selected version” section the code added the correct auto-add or admin-add type Roles to a tenant when it is created. You may also add some code to allow the user to upgrade to a higher version too. But in some applications, you might want to simply leave it to an admin user to do this.

Only an app-level admin user (i.e. a user that isn’t linked to a tenant and has permission to use the add / update tenants feature. That’s because the Roles that a tenant has defines what Roles the tenant users can have, and you don’t want to allow a tenant user to bypass your versioning.

The screenshot below shows three tenants:

  • 4U Inc. is an Enterprise version, with both the “Tenant Admin” (admin-add) Role and the “Enterprise” (auto-add) Role. (see the black tooltip with the two Role’s names when I hover the Tenant Role? column
  • Big Rocks Inc. is a Free version, and as such doesn’t have any tenant Roles
  • Pets Ltd. Is a Pro version, with only the “Tenant Admin” (admin-add) Role.

Conclusion

Many multi-tenant / SaaS applications use versioning, e.g. GitHub, Salesforce, Facebook, YouTube and even Twitter is now got a blue version (in some countries). Many applications offer free versions – GitHub for instance has a free version, but it has limitations which makes some companies pay for GitHub’s higher versions.

Versioning your multi-tenant application can will increase your potential user base and profits. The downside is it makes your application more complicated. The AuthP library will help, but it’s still takes careful work to create, market and manage different versions of your application.

The Invoice Manager application in the Example3 project in the AuthP repo is a great example to look at, and its designed to run using the localdb SQL Server that Visual Studio installs (or you can change the database connection string in the appsetting.json file if you want a different SQL Server). It automatically loads some demo data so that you can try various features written in the first three articles.

Happy coding.

Building ASP.NET Core and EF Core multi-tenant apps – Part2: Administration

Last Updated: April 21, 2022 | Created: January 11, 2022

In Part 1 of this series, you learnt how to set up the multi-tenant application database so that each tenant had its own set of data, which is private to them. This article explores the administration of a multi-tenant application and the different ways to add new users to a tenant.

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
  2. Administration: different ways to add and control tenants and users (this article)
  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

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

  • Any real application has a lot of code dedicated to administering the application and its users and staff to use that administration code to help your users.
  • Multi-tenant applications most likely have lots of users in many groups (called tenants), which make it important to think about making the admin quicker by automating the key pinch-points, like new companies wanting a new tenant and adding more users to their tenant.
  • The AuthP library is designed to spread some of the administration, such as adding new users to a tenant, down to a user in the tenant. This means a company (tenant) can self-manage their users.
  • The AuthP library relies on ASP.NET Core for registering a new user and securely logging a user in. AuthP uses the userId (a unique string) to add extra data to manage the tenants and what features in your application that a user can use.
  • I describe four ways to automate the linking a logged in use to a tenant. Some are good for users that sign up to your service at any time, and other approaches work for a company where users are registered to a common login service, like Azure Active Directory.
  • All the examples shown in this article come from the AuthP repo, especially from
  • Example3 which is a multi-tenant app, and Example5, which shows how to work with Azure Active Directory.
  • Here are links to various useful information and code.

Introduction to administration features

If you are building a multi-tenant application to provide a service to many companies, assuming you are charging for your service, you want lots of tenant and users. Even if you are building an application for a company then it must have lots of divisions to require a multi-tenant design. In both cases there is going to be a lot of administration to be done, such as setting up new tenants, adding user to tenants, altering what users can do as you application is improved.

If you don’t think about the admin parts right at the start you are going to have problems when lots of users join. I should know because I was asked by a client to design a multi-tenant application to handle thousands of users, and admin code was hard work. That’s why I built into the AuthP library ways to automate admin if I could, or pass some admin jobs to the owners of each tenant. But in the end, the features that can’t be automated have to manually executed by your staff.

This article contains both my approach to handling admin in a multi-tenant application and shows AuthP’s admin features and code in the AuthP repo that can automate some of the user admin features.

Administration use in an AuthP’s multi-tenant application

In an AuthP’s multi-tenant application you need to manage:

  • AuthP Roles, which define a group of features, e.g. creating, editing a Role.
  • AuthP Tenants, which controls the database filtering, e.g. creating a new tenant.
  • AuthP users, which defines the Roles and Tenant that each user has, e.g. adding a new user linked to a specific tenant.

NOTE: Read the original article, which explains what a Role is and how they control what features in your application that certain users can access.

A few of these features can be automated, but you ned some person / people to manage the tenants, users and what they can access. I refer to these users as admin users and they have Roles that allows them to access features that the normal users aren’t allowed to access, such as AuthP admin services.

My experience on a very large multi-tenant application suggests that some tenant owners would appreciate the ability to manage their own users, especially adding and removing users in their tenant which also relieves the load on your admin team. Therefore, the AuthP library support multiple types of admin users. The list shows these admin users ordered with the most useful first:

  • App admin: They have access to the admin services across all of the users and all of the tenants. NOTE: an app-level user is not linked to a tenant.
  • Tenant admin: They have limited access the admin services and can only see users in their tenant.
  • Customer support: In bigger applications it worth having some customer support (also known as help desk users). Their focus is on help tenant users with problems.
  • Super admin: When you first deploy your application you need a user to start adding more user, called the Super admin. AuthP library provides a way add that user.

But to make this work, with no possibility of the tenant admin accessing advanced features, you need a clear view of what each admin user can do. The next sections give you a guide to setting up each admin type.

NOTE: Part 3 in this series covers versioning your multi-tenant application which introduces the tenant Roles feature which provides another control over the Roles available in a tenant.

App Admin – the people who manage what users can do

App Admin users can potentially access every admin feature in your application, which means they need to understand what each admin function does and know what the consequences of each function.

Having said that, their day-to-day job is to manage:

  • Create, update and delete AuthP’s Roles. Only app admin users are allowed to manage the Roles because Roles control what features in your application a user can access.
  • Manage removing users / tenants where they have stopped using you application
  • They might have to manually create / update tenants, but as you will see later that can be automated.

App admins are the people that can see every Role, Tenant and User – something that the Tenant admin user can’t do. Here is a list of users that an admin user would see.

Tenant Admin – taking over some admin of users in their tenant

The tenant admin is there to manage the users in their tenant. This helps the tenant, because they don’t have to wait for the app admin to manage their users, and it helps reduce the amount of work on the app users to. Typical things a tenant admin user can do are:

  • List all the users in their tenant
  • Changing the Roles of the users in their tenant
  • Invite a user to join the tenant (see the invite a new user section link later in this article).

The AuthP’s method for listing the users requires the DataKey of the current user to be provided (see code below).

var dataKey = User.GetAuthDataKeyFromUser();
var userQuery = _authUsersAdmin.QueryAuthUsers(dataKey);
var usersToShow = await AuthUserDisplay
    .TurnIntoDisplayFormat(userQuery.OrderBy(x => x.Email))
    .ToListAsync();

If the DataKey isn’t null, then it only returns users with that DataKey, which means a tenant admin user will only see the users in their tenant. The screenshot below shows the tenant admin version of the user list. If you compare the app admin list of users (above) with the tenant admin’s version, you will see:

  • Only the users in the 4UInc tenant are shown
  • The tenant admin isn’t shown in the list
  • Instead of Edit & Delete the only thing they can do is change a user’s Roles.

NOTE: Some Roles contain powerful admin services, so in version 2 of the AuthP library these more powerful Roles are hidden from the tenant user. This means we can allow the tenant admin user to manage the tenant user’s Roles because any Roles they shouldn’t have access to are filtered out. This is described in Part 3.

Customer support – helping solve user’s problems

The other type of admin user staff you might have are customer support users (also known as help desk users). Customer support users are there to help the tenant users when they have problems. I cover this in more detail in the Part 4 article, which covers handling customer problems, but here is a overview of what the Part 4 article covers.

There are some things you can put into your application, like soft delete instead deleting directly from the database, that allows you to undo some things or at least find out who changed something they shouldn’t have. The AuthP library also has a feature that allows a customer support user to access the tenant’s data in a same way as the tenant user can. This allows the customer support user to see the problem from the user’s point of view.

The AuthP’s document page linking to a tenant’s data contains the ILinkToTenantDataService’s  method called StartLinkingToTenantDataAsync, which takes in the userId of the user that wants to link to the tenant, and the TenantId of the Tenant you want to access the data. This creates a cookie that overrides the app user’s DataKey (which is null) with the DataKey you want to access. Typically, I add this to the list of tenants, with an action next to each tenant which will set up the link – see this section in the document for a view of what that would look like.

Its important that a user should be very aware they are linked to a tenant’s data as, if they have the same access at a tenant they could create, update and delete data from the tenant. For that reason, I show an extra banner on the screen to warn the user and also show a button to stop linking – see this screenshot form the “Invoice Manager” application when the AppSupport@g1.com links to the “4U Inc.” tenant’s data.

NOTE: You can the updated section of the _layout.cshtml file to add the banner and “Stop Linking” button. It also stops linking if the user that is linking logs out. Note that you need to inject the service into the Razo page (e.g. @inject ILinkToTenantDataService LinkToTenantDataService).

This feature allows the customer support users to access a tenant’s features and data. Without this feature it would be very difficult to understand / fix customer problems. But must give the customer support users access to (some) of the tenant-level features, for instance the ability to list the invoices in the tenant. You can either add the same Roles as a tenant user would have to the customer support users, which will contains write and as read access, or make a special Role which only allows read-only access.  

SuperAdmin – solving the first deployment of your application

This admin user type solves the problem that when you deploy your application on first time there aren’t any user, because the database is empty. This means you can’t log in to add users – this is a catch-22 problem.

The AuthP library has a solution to this using its bulk load feature to add a Super Admin user who has access to all the application’s features that are protected by the HasPermission attribute / method. This documentation details the process to create a Super Admin user when deploying an application for the first time.

Managing your users in your tenants

We started with the higher-level administration feature, like setting up Roles and deleting tenants, but the day-to-day admin job is having new users creating or joining a tenant. If possible, you want to automate as much as you can to a) make it easier to a company sign up to your application, and b) to minimise what the app admin has to do. In this section I describe different approaches for adding tenant users, with the first two automating the process.

But before we look at the different ways to add tenant users, we need to talk about how ASP.NET Core manages users. ASP.NET Core breaks up what a user can do into two parts:

  1. Authentication features, which handles registration of a new use and a secure login of an existing user.
  2. Authorisation features, which controls what features the logged-in user can use in your application.

ASP.NET Core’s authentication features a few authenticate providers like the individual user accounts service, but ASP.NET Core also supports OAuth 2.0 and OpenID, which are the industry-standard protocol for authorization, which supports Azure Active Directory, Google login and many many more. This provides a large set of ways to authenticate a user when they are logging in.

And the AuthP library provides the authorization part of a multi-tenant application (see the first article which explains this). AuthP stores each user’s authorization data, such the user’s link to a tenant, in its own DbContent and AuthP has admin code to manage the AuthP authorization date. The link between the authenticated logged-in user and AuthP authorization data is via the authorization user’s Id, referred to as the userId.

This gives you the best of both worlds. You want Microsoft, Google etc to handle the authentication of the user, with all the problems of hacking etc., while the AuthP library provide the extra authorization features to work with more complex situations, like a multi-tenant application.

How a authenticated user is linked to AuthP authorization data depends on whether the list of authenticated users can be read or not. For instance, if you use an Azure Active Directory (Azure AD) you can arrange access to the list of users, while if you are using Google as the source of authenticated user you can’t read the list of all the uses. Here are four ways to handle adding users.

  1. Take over the registering of a new user, for instance code that adds the new user to the individual user accounts authentication provider and then creates the AuthP’ user too. This a good approach when you want your users sign up to your multi-tenant app.
  2. Have the tenant admin user invite a new user to join a tenant. This approach allows an admin tenant user sends a url to a user which when clicked adds the registered the user (authentication) and sets up their tenant and Roles (authorization).
  3. Syncing AuthP’s users against the authenticated list of users, for instance if you use an Azure Active Directory (Azure AD). This needs a source of authenticated user you can read and works best when you have a fairly static list of users, say within an application used by a company.
  4. Handle external login providers, for instance if you allow users to login via Google. This a good approach if you want to allow your users to log in using their Facebook, Twitter, Google etc. logins.

The next four sections explain each approach.

1. Take over the registering of a new user

This works if you have a private authentication provider, such as ASP.NET Core’s individual user accounts authentication provider, Azure AD, Auth0, etc. It also works well if you want new users to register / pay for the use of your application.

The steps in implementing the register a new user are:

  1. Ask for the user for the information you need to register with your private authentication provider, e.g. email and password.
  2. Register this user as a new user in your private authentication provider and obtain the new user’s userId.
  3. Now add a AuthP user, using the email / name and the userId of the registered authentication user.
  4. In a multi-tenant application, you could create a new tenant too, as the user has paid for the use of your application. Also, you can make this user the admin tenant for the new tenant which allows them to invite further users (if that feature is enabled).

Here the code in the AddUserAndNewTenantAsync method in the UserRegisterInviteService. NOTE: I made some simplification to make it easier to understand the code.

//Add a new user, in this case using individual users account
var userStatus = await GetIndividualAccountUserAndCheckNotAuthUser(
    dto.Email, dto.Password);
if (status.CombineStatuses(userStatus).HasErrors)
    return status;

//Now we can create the tenant
var tenantStatus = await _tenantAdminService.AddSingleTenantAsync(
     dto.TenantName);
if (status.CombineStatuses(tenantStatus).HasErrors)
    return status;

//This creates an AuthP user
status.CombineStatuses(await _authUsersAdmin.AddNewUserAsync(
    userStatus.Result.Id, dto.Email, null, roles, dto.TenantName));

The screenshot below shows a new user wanting to sign up to the Invoice Manager. There are three versions to choose with different features and prices. Once the user picks one of the versions (NOTE: versioning your application is covered in Part 3 for this series) they provide their email and password, plus the name of the company to use as the tenant name. Then steps 2 to 4 are run and the user logs in to their new tenant.

Example3 in the AuthP repo provides an example of this approach. A new user can pick which version of the application they want and, after “paying” for the version, then the steps shown above are executed. The code can be found in the AddUserAndNewTenantAsync method in the UserRegisterInviteService class.

NOTE: You can try this yourself. Clone the AuthP repo and run the Example3 ASP.NET Core project. On the home page there is a “Sign up now!” tab in the NavBar with will take you to the “Welcome to the Invoice Manager” page where you can select which version you want and then give an valid email and password plus the name you want to have for your tenant.

2. Have the admin user invite a new user to join a tenant

Just like the last approach, this works if you have a private authentication provider, such as ASP.NET Core’s individual user accounts authentication provider, Azure AD, Auth0, etc. This allows a tenant admin (or an App admin) to send an invite to a new user to join a tenant.

The steps in implementing the invitation to a new user to a tenant are:

  1. The tenant admin user creates a url to send to a user to join the admin’s tenant. The URL contains an encrypted parameter of the user’s email and the primary key of the tenant.
  2. The tenant admin emails this url to the user that they want to join the tenant.
  3. When the new user clicks the url they are directed to a page which askes for their email and password, plus the hidden encrypted parameter from the url.
  4. The code then:
    1. Checks that the encrypted parameter contains the same email as the tenant admin user provided in step 1 so that the link has been copied / hijacked.
    1. Registers a new user in your private authentication provider using the data provided by user in step 2. From this you can obtain the new user’s userId.
    1. Adds a AuthP user, using the email / name and the userId of the registered authentication user with a link to the tenant defined in the invite.

The code for this is also the UserRegisterInviteService class.

Step 1, creating encrypted parameter code

The code in the UserRegisterInviteService uses the AuthP’s IEncryptDecryptService to create the parameter (see below). Encrypting the invite means that only the person with the email can use the invite, and information about the tenant isn’t leaked if the email is intercepted.

public string InviteUserToJoinTenantAsync(
    int tenantId, string emailOfJoiner)
{
    var verify = _encryptorService.Encrypt(
        $"{tenantId},{emailOfJoiner.Trim()}");
    return Base64UrlEncoder.Encode(verify);
}

Then, in the controller I use the code below to create the url to go into the email sent to the user that you want to invite

//Thanks to https://stackoverflow.com/questions/30755827/getting-absolute-urls-using-asp-net-core
public string AbsoluteAction(IUrlHelper url,
    string actionName,
    string controllerName,
    object routeValues = null)
{
    string scheme = HttpContext.Request.Scheme;
    return url.Action(actionName, controllerName, 
           routeValues, scheme);
}

The figure below shows the pages for the first two steps in this approach.

Step 4 adds the new user to the tenant code,

The AcceptUserJoiningATenantAsync method code shown below finishes the step 4. NOTE: the code has been heavily simplified to make it easier to understand.

public async Task<IStatusGeneric<IdentityUser>>
    AcceptUserJoiningATenantAsync(
    string email, string password, string inviteParam)
{
    var status = new StatusGenericHandler<IdentityUser>();

    // …decrypt of encrypted inviteParam and various checks left out 
    
    // find the tenant from the encrypted tenantId parameter
    var tenant = await _tenantAdminService.QueryTenants()
        .SingleOrDefaultAsync(x => x.TenantId == tenantId);

    //Add a new individual users account user
    var userStatus = await GetIndividualAccountUserAndCheckNotAuthUser(
        email, password);
    if (status.CombineStatuses(userStatus).HasErrors)
        return status;

    //This creates an AuthP user
    status.CombineStatuses(await _authUsersAdmin.AddNewUserAsync(
        userStatus.Result.Id, email, null,
        roles, tenant.TenantFullName));

    //… final checks left out
}

NOTE:  You can try this by running the Example3 ASP.NET Core project and logging in using any of the seeded tenant admins, e.g. admin@4uInc.com (password = email), and then click the “Invite User” on the NavBar. This will take you to the “Welcome to the Invoice Manager” page where you can select which version you want and then give your email and password plus the name you want to have for your tenant.

3. Syncing AuthP’s users against the authenticated list of users

This needs a source of authenticated user you can read and works best when you have a fairly static list of users, say within an application used by a company. Also, only an app admin can use this approach as you need to set up which tenant a new user should be linked to.

To use the sync approach, you have to:

3.a Implement / register AuthP’s ISyncAuthenticationUsers service

The ISyncAuthenticationUsers requires you to create a service that will return a list of users with the UserId, Email, and UserName from the authenticated users. The code below shows the sync service for an individual user accounts authentication provider.

public class SyncIndividualAccountUsers : ISyncAuthenticationUsers
{
    private readonly UserManager<IdentityUser> _userManager;

    public SyncIndividualAccountUsers(UserManager<IdentityUser> userManager)
    {
        _userManager = userManager;
    }

    /// <summary>
    /// This returns the userId, email and UserName of all the users
    /// </summary>
    /// <returns>collection of SyncAuthenticationUser</returns>
    public async Task<IEnumerable<SyncAuthenticationUser>> 
        GetAllActiveUserInfoAsync()
    {
        return await _userManager.Users
            .Select(x => new SyncAuthenticationUser(x.Id, x.Email, x.UserName))
            .ToListAsync();
    }
} 

NOTE: If you want to use an Azure AD for authenticating users, then see the SyncAzureAdUsers service and the video “AuthPermissions Library – Using Azure Active Directory for login” for how to get permission to read access the Azure AD.

3.b Add a “Sync users” frontend feature

AuthP’s user admin code contains a method called SyncAndShowChangesAsync, returns the differences (new, change and deleted) between the list of authenticated users and AuthP’s list of users. Typically, you would show these changes before you apply the changes to the AuthP’s users. Here is an example of the type of display you might see.

To apply all the changes to the AuthP’s user clicks the “update all” button that calls the method ApplySyncChangesAsync.  At that point the list of AuthP users is in sync with the authenticated users, but any new users might need Role and / or tenant setting up.

NOTE: Read the documentation on Synchronizing the AuthUsers which gives more information on how to do this, plus links to example code for showing / altering the changes.

4. Handing external login providers

The final approach allows you to use external authentication providers such as Google, FaceBook, Twitter etc. This might be more attractive to your users, who don’t have to remember another password.

You should take over the external authentication provider login so that you can check if an AuthP user with that email exists. If a AuthP user doesn’t exist, you need to take the user through a process that links them to a tenant (maybe paying for the service too).

Once an AuthP user exists and set up you need to intercept the OAuth 2.0 authentication to add the AuthP claims. You do this via ASP.NET Core’s OAuth 2.0 OnCreatingTicket event and get the IClaimsCalculator service. This service contains the GetClaimsForAuthUserAsync method which takes in the user’s userId and will return claims to be added to the logged in user.

Useful links on this approach are:

Conclusion

Adding proper administration features to an application is always important. But when your application is multi-tenant application you want your tenants to find your application is easy to use. And on the other side you need to control the admin features such that a tenant can’t break or bypass the intended features in the tenant.

And in a multi-tenant application it’s not just logging in, its also about adding a new user to a tenant. That’s why I explained the “take over the registering of a new user” and the “Have the tenant admin user invite a new user to join a tenant” first – they take a bit more code, but they automate adding of a new user, which removes the load on your admin people.

In the next article you will learn about AuthP’s tenant Roles, which gives you more control of what a tenant user can do. In particular you can offer different versions of your code, e.g. Free, Pro, Enterprise, which might help to reach more people, some of which will pay extra for the extra features in your higher levels.

I hope this and the other articles in this series help you in understanding how you might build multi-tenant application. The AuthP library and the AuthP repo contain a large body of admin features, useful example applications, plus lots of documentation.

Happy coding.

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

Last Updated: April 17, 2022 | Created: January 4, 2022

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

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

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 sitations 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}");
            }
        }
    }
}

NOTE: 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.

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.

How to safely apply an EF Core migrate on ASP.NET Core startup

Last Updated: December 1, 2021 | Created: December 1, 2021

There are many ways to migrate a database using EF Core, and one of the most automatic approaches is to call EF Core’s MigrateAsync method on startup of your ASP.NET Core application – that way you don’t forget it.  But the “migrate on startup” approach has a big problem if your ASP.NET Core is running multiple instances of your app (known as  Scale Out in Azure and Horizontally Scaling in Amazon). That’s because trying to apply multiple migrations at the same time doesn’t work – either it will fail or worse, it might cause data corruption to your database.

This article describes a library designed to updates global resources, e.g., a database, on startup of your application that handles applications that have for multiple instances running. Migration is a one thing it can do, but as you will see there are other examples, such as adding an admin user when you first deploy your application.

This open-source library is available as the NuGet package called Net.RunMethodsSequentially and the code is available on https://github.com/JonPSmith/RunStartupMethodsSequentially. I use the shorter name of RunSequentially to refer to this library.

TL;DR; – Summary of this article

  • The RunSequentially library manages the execution of the code you run on the startup of your ASP.NET Core application. You only need this library when you have multiple instances running on your web host because it will make sure your startup code, which in every instance, aren’t run at the same time.
  • The library uses a lock on a global resource (i.e. an resource all the app instances can access) which your startup code are executed serially, and never in parallel.  This means you can migrate and/or seed your database without nasty things happening, e.g. running multiple EF Core Migrations at a same time could (will!) causes problems.
  • But be aware, each instance of your applications will run your startup code, the library just guarantees they won’t run at the same to. That means if you are seeding a database you need to check it hasn’t already been added.
  • To use the RunSequentially library you have to do three things:
    • You write the code you want to run within a global lock – these are referred to as startup services.
    • Select a global resource you can lock on – a database is a good fit, but its not yet created you can fall back to locking on a FileSystem Directory, like ASP.NET Core’s wwwRoot directory.
    • You add code to the ASP.NET Core  configuration code in the Program class (net6) to register and configure the RunSequentially setup.
  • Because the RunSequentially library uses ASP.NET Core’s HostedService its run before the main host starts, which is perfect. But the HostedServices doesn’t give good feedback if you have an exception in the RunSequentially code. To help with this I have included a tester class in the library to which allows you to check your code / configuration works before you publish your application to production.

Setting the scene – why did I create this library?

Back in December 2018 I wrote an article called “A better way to handle authorization in ASP.NET Core” that described several additions I added to ASP.NET Core to provide management of users in a large SaaS (Software as a Service) I created for a client. This article is still the top article on my blog three years on, and many people have used this approach.

Many developers asked me to create a library containing the “better ways” features, but there were a few parts that I didn’t know how do in a generic library that anyone could use. One of parts was the adding setup data to the database when there were multiple instances of ASP.NET Core were running. But in March 2021 GitHub @zejji provided a solution I hadn’t known about using the DistributedLock library.

The DistributedLock library made it possible to create a “better ways” library which is called AuthPermissions.AspNetCore (shortened to as AuthP). The AuthP library is very complex, so I released version 1 in August 2021 which only works with single instances of ASP.NET Core, knowing I had a plan to handle multiple instances. I am currently working version 2 which is using the RunSequentially library.

Rather than put the RunSequentially code directly into the AuthP library I created its own library, which means other developers can use RunSequentially library to use the “migrate on startup” approach on applications that has have multiple instances.

How the RunSequentially library works, and what to watch out for

The library allows you create services which I refer to as startup services which are run sequentially on startup of your application. These startup services are run within a DistributedLock global lock which means your startup services can’t run all at the same time but are run sequentially. This stops the problem of multiple instances of the application trying to update one common resource at the same time.

But be aware every startup service will be run on every application’s instance, for example if your application is running three instances then your startup service will be run three times. This means your startup services should check if the database has already been updated, e.g. if your service adds an admin user to the the authentication database it should first check that that admin user isn’t already been added (NOTE: EF Core’s Migrate method checks if the database needs to be updated, which stops your database being migrated multiple times).

Another warning is that you must NOT apply a database migration that changes the database such that the old version of the application’s software won’t work (these types of migrations are known as a breaking change) cannot be applied to an application which is running multiple instances. That’s because Azure etc. replace each of the running instance one by one, which means a migration applied by the first instance that has breaking changes will “break” the other running instances that hasn’t yet have its software updated. 

This breaking change issue isn’t specific to this library but because this library is all about running applications that have multiple instances, then you MUST ensure you not applying a migration that has a break change  (see the five-stage app update in this article for how you should handle a breaking changes to your database).

Using the RunSequentially library in your ASP.NET Core application

This breaks down into three stages:

  1. Adding the RunSequentially library to your application
  2. Create your startup services, e.g. one migrate your database and say another to add an admin user.
  3. Register the RegisterRunMethodsSequentially extension method to your dependency injection provider, and select:
    1. What global resource(s) you want to lock on.
    1. Register your startup service(s) you want run on startup.

Let’s look at these in turn.

1. Adding the RunSequentially library to your application

This is simple, you add the NuGet package called Net.RunMethodsSequentially into your application. This library uses the Net6 framework.

2. Create your startup services

You need to create what the RunSequentially library calls startup services, which contain the code you want to apply to a global resource. Typically, it’s a database but it could be a common file store, azure blob etc.

To create a startup service that will be run while in a lock you need to create a class that inherits the IStartupServiceToRunSequentially interface. This has a method defined has a ValueTask ApplyYourChangeAsync(IServiceProvider scopedServices), which is the method where you put your code to update a shared resource. The example code below is a RunSequentially startup service, i.e. it implements the IStartupServiceToRunSequentially interface, and shows how you would migrate a database using EF Core’s MigrateAsync method.

public class MigrateDbContextService : 
    IStartupServiceToRunSequentially
{
    public int OrderNum { get; }

    public async ValueTask ApplyYourChangeAsync(
        IServiceProvider scopedServices)
    {
        var context = scopedServices
            .GetRequiredService<TestDbContext>();

        //NOTE: The Migrate method will only update 
        //the database if there any new migrations to add
        await context.Database.MigrateAsync();
    }
}

The ApplyYourChangeAsync method has a parameter holding a scoped service provider, which is a copy of the normal services used in the main application. From this you can get the services you need to apply your changes to the shared resource, in this case a database.

NOTE: You can use the normal constructor DI injection approach, but as a scoped service provider is already set up to run the RunSequentially code you might like to use that service provider instead.

The OrderNum is a way to define the order you want your startup services are run. If startup services have the same OrderNum value (default == zero), the startup services will be run in the order they were registered.

This means most cases you just register them in the order you want them to run, but in complex situations the OrderNum value can be useful to define the exact order your startup services are run in via a OrderBy(service => service.OrderBy)inside the library. For example, my AuthP library some startup services are optionally registered by the library and other startup services are provided by the developer: in this case the OrderNum value is used to make sure the startup services are run in the correct order.

3. Register the RegisterRunMethodsSequentially and your startup services

You need to register the RunSequentially code and your startup services with your dependency Injection (DI) provider. For ASP.NET Core this the configuration of the applciation happens in the Program class (net6) (previously in the Startup class before net6). This has three parts:

  1. Selecting/registering the global locks.
  2. Registering the RunSequentially code with your DI provider
  3. Register the startup services

Before I describe each of these parts the annotated code below shows process.

NOTE: The above was taken from the Program class in a test ASP.NET Core in the RunSequentially’s repo.

And now the detail of the three parts.

3.1 Selecting/registering the global locks

The RunSequentially library relies on creating a lock on a resource that all the web application’s instances can access, and it uses the DistributedLock library to manage these global locks. Typical global resources are databases (DistributedLock supports 6 database types), but the DistributedLock library also supports a FileSystem Directory and Windows WaitHandler.

However, the RunSequentially library only natively supports the three main global lock types, known as TryLockVersions, and a no locking version, which is useful in testing:

  • SQL Server database, AddSqlServerLockAndRunMethods(string connectionString)
  • PostgreSQL database, AddPostgreSqlLockAndRunMethods(string connectionString)
  • FileSystem Directory, AddFileSystemLockAndRunMethods(string directoryFilePath)
  • No Locking (useful for testing): AddRunMethodsWithoutLock()

NOTE: If you want to use another lock type in the DistributedLock library you can easily create your own RunSequentially TryLockVersion version. They aren’t that hard to write, and you have three versions in the RunSequentially library to copy from.

GitHub @zejji pointed out there is a problem if the database isn’t created yet, so I created a two-step approach to gaining a lock:

  1. Check if the resource exists. If doesn’t exist, then try the next TryLockVersion
  2. If the resource exists, then lock that resource and run the startup services

This explains why I use two TryLockVersion in my setup: the first tries to access a database (which is normally already created), but if the database doesn’t exist, then it uses the FileSystem Directory lock (NOTE: the FileSystem Directory lock can be slower than a database lock – see this comment in the FileSystem docs, so I use the database lock first).

So, putting this all together the code in your Program class would look something like this.

var builder = WebApplication.CreateBuilder(args);

// … other parts of the configuration left out.

var connectionString = builder.Configuration
     .GetConnectionString("DefaultConnection");
var lockFolder = builder.Environment.WebRootPath;

builder.Services.RegisterRunMethodsSequentially(options =>
    {
        options.AddSqlServerLockAndRunMethods(connectionString);
        options.AddFileSystemLockAndRunMethods(lockFolder);
    })…
// … other parts of the configuration left out.

The code in question are:

  • Lines 5 to 7: This gets the connection string for the database, and the FilePath for the application’s wwwroot directory.
  • Lines 11 to 12: These register two TryLockVersion versions. It first will try to create a global the database, but if the database hasn’t been created yet it will try to create a global lock on the wwwroot directory.

3.2 Registering the RunSequentially code with your DI provider

The RunSequentially library needs to register the GetLockAndThenRunServices as an ASP.NET Core IHostedService, which is run after the configuration, but before the main web host is run (see Andrew Lock’s useful article that explains all about IHostedService). This is done via the RegisterRunMethodsSequentially extension method.

NOTE: For non-ASP.NET Core applications, and for unit testing, you can set the RegisterAsHostedService properly in the options to false. This will register the GetLockAndThenRunServices class as a normal (transient) service.

See the code shown in the last section to see that the  RegisterRunMethodsSequentially extension method takes in the builder.Services so that it can register itself and the other parts of the RunSequentially parts.

3.3 Register the startup services

The final step is to register your startup services, which you do using the RegisterServiceToRunInJob<YourStartupServiceClass> method. There are a couple of rules designed to catch error situations. The library will throw an RunSequentiallyException if:

  • You didn’t register any startup services
  • If you registered the same class multiple times

As explained in the 2. Create your startup services section by default your startup service are run in the order they are registered. For instance, you should register your startup service that migrates your database first, followed by registering a startup service that accesses that database.

Using the “run startup services in the order they were registered” approach works in most cases, but my AuthP library I needed a way to define the run order, which is why in version 1.2.0 I added the OrderNum property as described in 2. Create your startup services section.

Checking that your use of RunSequentially will work

I have automated tests for the RunSequentially library, but before I revealed this library to the world, I wanted to be absolutely sure that an ASP.NET Core application using the RunSequentially library worked on Azure with was scaled out to multiple instances. I therefore created a test ASP.NET Core where I could try this out on Azure.

NOTE: You can try this out yourself by cloning the RunStartupMethodsSequentially repo and running the WebSiteRunSequentially ASP.NET Core project, either on Azure with scale out, or on your own development PC using the approaches shown in this stack overflow answer.

The WebSiteRunSequentially web app didn’t work at first because I hadn’t set the Azure Database Firewall properly, which causes an exception. The library relies on ASP.NET Core’s IHostedService to run things before the main host starts, but if you have an exception you don’t get any useful feedback!. So, I decided to add a tester that allows you to copy the RunSequentially code in your Program class and run it in an automated test.

The code below is from one of my xUnit Test projects, where I copied the setup code from the WebSiteRunSequentially Program class and put in a test. I did have to make a few changes to get a connection string and the tester class provides a local directly instead of the ASP.NET Core wwwRoot directory. Other than those two changes its identical to the Program class code.

[Fact]
public async Task ExampleTester()
{
    //SETUP
    var dbOptions = this.CreateUniqueClassOptions<WebSiteDbContext>();
    using var context = new WebSiteDbContext(dbOptions);
    context.Database.EnsureClean();

    var builder = new RegisterRunMethodsSequentiallyTester();

    //ATTEMPT
    //Copy your setup in your Program here 
    //---------------------------------------------------------------
    var connectionString = context.Database.GetConnectionString();  //CHANGED
    var lockFolder = builder.LockFolderPath;                        //CHANGED

    builder.Services.AddDbContext<WebSiteDbContext>(options =>
        options.UseSqlServer(connectionString));

    builder.Services.RegisterRunMethodsSequentially(options =>
    {
        options.AddSqlServerLockAndRunMethods(connectionString);
        options.AddFileSystemLockAndRunMethods(lockFolder);
    })
        .RegisterServiceToRunInJob<StartupServiceEnsureCreated>()
        .RegisterServiceToRunInJob<StartupServiceSeedDatabase>();
    //----------------------------------------------------------------

    //VERIFY
    await builder.RunHostStartupCodeAsync();
    context.CommonNameDateTimes.Single().DateTimeUtc
        .ShouldBeInRange(DateTime.UtcNow.AddSeconds(-1), DateTime.UtcNow);
}

The RegisterRunMethodsSequentiallyTester class is in the RunSequentially library so it’s easy to access. But be aware, while the code is the same, but you might not use the same database as you production system so this type of test might still miss a problem.

If you want a much more complex test, then look at the last test in the AuthP’s TestExamplesStartup test class. This shows you might have a bit of work to register the other parts of the system to make it work.

NOTE: You might find my EfCore.TestSupport library helpful as it has methods to set up test databases etc. That’s what I used at the start of the test shown above.

Having fixed the Firewall, I published the WebSiteRunSequentially web app to an Azure App Service plan with the scale out was manually set to three instances of the application. Its job is to update a single common entity and also added logs of what in done in another entity. The screenshot shows the results.

I restarted the Azure App Service, as that seems to reset all the running instances, and the logs tells you what happens:

  1. The first instance of application runs the “update startup service” and it found that common entity has been updated a while ago (I set a time of 5 minutes), so assumes it’s the first to service to be run in the set of three so it sets the Common entity’s Stage to 1.
  2. When the second instance of application is allowed to start up it runs the same “update startup service”, but now it finds that the common entity was updated four seconds ago, so it assumes that another instance had just updated it and updated the increments the Stage by 1, which in this case makes Stage equal 2.
  3. When the third (and final) instance of application start up the “update startup service” it too finds that the common entity was updated about 2 minutes ago, so it assumes that another instance had just updated so it assumes that another instance had just updated it and updated the increments the Stage by 1, which in this case makes Stage equal 3.

That process and results shows me that the RunSequentially library works as expected, and at the same time I learnt a few things to watch out for. For instance, I hadn’t set the Azure Database Firewall properly, which causes an exception before the ASP.NET Core host was up. This meant I only got a generic “its broken” with no information, which is why I added the tester class into the RunSequentially library.

Conclusion

The RunSequentially library is designed to run code at startup that won’t interfere with the same code run in other instances of your ASP.NET Core application. The RunSequentially library is small, but it still provides an excellent solution to handle updating global resource, with the “migrate on startup” being one of the main uses. Its also quite quick, the SQL Server check-and-lock code only took 1 ms on a local SQL Server database.

I’m also using the RunSequentially library in my AuthPermissions.AspNetCore library in a quite complex setup. This can have something like six different startup services available for migrating / seeding various parts of the database: some registered by the AuthP code and some added by the developer. Making that work property and efficiently requires a couple iterations of the RunSequentially library (currently at version 1.3.0) and only now I am recommending RunSequentially for real use, although there have been nearly 500 downloads of the NuGet already.

I’m interested in what else other developers might use this library for. I have an idea of managing what ASP.NET Core’s BackgroundService across applications with multiple instances. At the moment the same BackgroundService will run in each instance, which is either wasteful or actually causes problems. I could envision a system using a unique GUID in each instance of the application and a database entity like the one in my test ASP.NET Core app to make sure certain BackgroundServices would only run in one of the application’s instances. This would mean I don’t have to fiddle with WebJobs anymore, which would be nice 😊.

If you come up with a use for this library that outside the migrate / seed situations, then please leave a comment on this article. I, and other readers might find that useful.

Updating your ASP.NET Core / EF Core application to NET 6

Last Updated: November 17, 2021 | Created: November 17, 2021

With the release of NET 6 I wanted to update the code in the repo called EfCoreinAction-SecondEdition which contains the code that goes with my book “Entity Framework Core in Action 2nd edition”. This code is based on ASP.NET Core 5 and EF Core 5 and this article describes what I had to do to update the NET 5 version of the code to NET 6, plus a look at any performance improvements.

TL;DR; – Summary of this article

  • I updated a non-trivial ASP.NET Core / EF Core application (22 projects) from EF Core 5 to EF Core 6. Overall, it was pretty easy and only took a few days.
  • You have to use Visual Studio 2022 (or VSCode) – Visual Studio 2019 doesn’t work
  • Any project using ASP.NET Core / EF Core NuGet packages has to have a target framework of net6.0.
  • My ASP.NET Core MVC updated with no changes to the code.
  • The EF Core parts, which as complex, had two compile errors on unusual parts of EF Core and a few runtime errors, mainly around the Cosmos DB changes.
  • I didn’t find any performance improvements, but that’s because my queries were SQL database bound, taking between 10 ms. to 800 ms. to get a result.

Setting the scene – what type of app did I update?

In part 3 of my book, I build a complex ASP.NET Core / EF Core application aimed at testing the performance of EF Core. This code is in the Part3 branch and contains 22 projects that use quite of the more intricate parts of EF Core features such as Global Query Filters, user-defined functions, table splitting, transactions and so on. This means any EF Core breaking changes are likely to been seen on this application. My aim was to:

  1. Convert my book setting web site to NET 6 and get it compile.
  2. Make the 200 ish xUnit tests to run successfully
  3. To make the application run
  4. Test the performance of the updated application

NOTE: I did NOT plan to change the code to use new NET 6 features, such as ASP.NET Core simplified  Program class or EF Core features such as the new SQL Server Temporal Tables support or improved Cosmos DB support. That’s for another day.

The resulting code can be found in the Part3-Net6 branch, and you can look at all the changes by comparing Part3 branch with Part3-Net6 branch.

The list of things I had to do in order

Overall, the update from EF Core 5 / ASP.NET Core was fairly easy. I didn’t have to change any of the ASP.NET Core project, other than changing its target framework to net6.0 and updating all the NuGet packages. I would say getting the right order for the first four steps weren’t obvious (old articles said I could use Visual Studio 2019, but it can’t).

Here is a step-by-step list to each step, with links to each part

  1. Download Net6 SDK and Visual Studio 2022
  2. Update projects that have EF Core / ASP.NET Core packages in them
  3. Update your NuGet packages to version 6
  4. Fix any compile errors caused by the version 6 update
  5. Run your unit tests and fix any changes
  6. Run the application and check it works
  7. Did performance improve?

1. Download Net6 SDK and Visual Studio 2022

To start, you need to download and install the correct NET 6 SDK for your development machine.

You also need to download Visual Studio 2022, as Visual Studio 2019 doesn’t support Net 6. Then run Visual Studio 2022 and open your application. Visual Studio 2022 does support Visual Studio 2019 solutions so you can run any tests or check your application before you start the upgrade.

TIP: Make a new branch for the changes, as its useful to check back to the older version if something doesn’t work.

2. Update projects that have EF Core / ASP.NET Core packages in them

To update EF Core to version 6 requires the projects that use EF Core / ASP.NET Core NuGet packages to have a target framework of net6.0. ASP.NET Core tends to be in one single project, but EF Core is likely to be in multiple projects.

Before EF Core 6 came out you could use netstandard2.1 for EF Core 5, or netstandard2.0 for EF Core 3.1 or below. But with EF Core 6 you must update the project’s target framework to net6.0 before you try to update to EF Core 6, otherwise the EF Core 6 NuGet updates will fail.

To update a project in Visual you click on the project which will open the project’s .csproj file. You can then edit the TargetFramework part to Net6.0.

Updating projects to net6.0 has ramifications if you are using any kind of layered architecture, because a project using the netstandard2.1 framework can’t have a project reference to a net6.0 project. In my BookApp I was using a clean architecture approach, so the inner Domain projects (called entities in clean architecture)  didn’t contain any EF Core and could use the netstandard2.1 framework, but other than I had to change all the projects to net6.0.

NOTE: This is part of the change over to .NET and going forward you will be using named .NET versions (e.g. net6.0, net7.0) much more in the lower layers in your applications.

3. Update your NuGet packages to version 6

Once your packages are changed net6.0, then you can use the “Manage NuGet Packages for Solutions”, by right-clicking on the “Solution” at the top of the Solution Explorer. When you are in the NuGet Package Manager select the “Updates” tab which should provide a list of NuGet packages already installed in your application, which suggestions for newer NuGet versions.

As well as EF Core upgrades there may be other NuGet packages you use, such as open-source libraries that you find useful. Be a little careful of libraries that use EF Core inside, as some of them might work. For instance, I have about ten libraries that work with .NET and four of them needed to be updated, but the rest were OK. I didn’t find that until I tried to use them, so check any code that uses an external library that haven’t said it works with net 6 – a lot will work, but some might not.

4. Fix any compile errors caused by the update

You shouldn’t find many breaking changes that causes a compile error, but I did have two, but they are unusual parts of EF Core.

  • One was a change in the IModelCacheKeyFactory signature – I had to add a new parameter
  • Another was around the use of EF Core’s FromSqlRaw method. Because my app works with both SQL Server and Cosmos DB database types I got a compile time error CS0121, “The call is ambiguous between the following methods or properties” (see EF Core issue #26503), which I fixed.

5. Run your unit tests and fix any changes

When I ran my xUnit tests and out of 200 tests, of which the majority are integration tests using EF Core, and I had only a few errors. The biggest was around the changes in EF Core 6’s improvements of handling Cosmos DB.

  • There was a change to the configuration of collections of owned types, which I needed for my Cosmos DB code. This has changed (for the better) in EF Core 6.
  • The whole handing of Cosmos DB create / update / delete exceptions had been improved, so I had to change that code.
  • I had some problems when using FromSqlRaw method with Cosmos DB.  That’s because there are big improvements to Cosmos DB support in EF Core 6. I fixed the Cosmos DB code, but I didn’t upgrade my code to use the new features because it’s a lot of work. I hope to look at this later.
  • The last test that failed was checking for a bug in EF Core 5 (see EF Core issue #22701) which I was checking for so I would know when it was fixed. I’m glad to see that bug was fixed, and I removed the unit test that was there to alert a change.

NOTE: If you want to test code that uses EF Core, I suggest you look at my library EfCore.TestSupport. This has a lot of useful features that speeds up the writing of tests that need access to your application’s DbContext. Also, my book “Entity Framework Core in Action” has a whole chapter about testing code that uses EF Core.

6. Run the application and check it works

xUnit tests are great, but there are lots of things that are hard to test, especially some of ASP.NET Core features. The only problem I found was my analysis code relied on logging data, and ASP.NET Core had changed its name / event code. I didn’t test every minor point but I did some detailed performances tests and I didn’t find any other problems.

Did performance improve?

EF Core 6 has a lot of work done to reduce the overheads of converting LINQ to SQL and they know it has improved the performance and I was interested to see if it made any effect to the a web site for selling book that I created in chapters 15 and 16 (see this EF Core Community Standup video where I cover performance tuning).

I retested my performance tests with EF Core 6, and I didn’t find any improvements. Thinking about it I realised my performance tests took between 10 ms. to 800 ms. of database access time, which means any improvements to the code overhead wouldn’t make any effect. I did try small queries taking 1 to 3 ms. and there was possibly an improvement, but because the logging only works in 1 ms. steps I wasn’t able to be sure.

So, my takeaway is that reducing the overheads EF Core code is a good thing, but don’t expect EF Core 6 to suddenly improve your really slow database accesses.

Conclusion

Overall, updating my book selling web app to net6.0 took a few days, which I think is quite good. I did waste time trying to use Visual Studio 2019 to work with net6.0 because of old articles said it did support net6.0, which isn’t true now – need Visual Studio 2022. I also found (the hard way) I had to update the projects to net6.0 before I could load the new net6 NuGet packages.

My integration tests were wonderful as they pointed out the parts that didn’t work, especially with the large changes/improvements to Cosmos DB. Finding those issues would have very difficult using manual testing because they are only triggered in errors states, which is difficult to do manually.

I’m also really pleased with the improvements such as SQL Server temporal tables, migration bundles, global model config and of course the better support of Cosmos DB (see “what new in EF Core 6” for the full list). I look forward to using EF Core 6 in my future applications.

Happy coding.

Using PostgreSQL in dev: Part 2 – Testing against a PostgreSQL database

Last Updated: November 17, 2021 | Created: November 10, 2021

This article describes how to test an application that use Entity Framework Core (shortened to EF Core) that accesses a PostgreSQL database. I describe various methods that help you to write xUnit tests that have to access a PostgreSQL database. These new PostgreSQL test methods can be found in the new version of my EfCore.TestSupport library (5.1.0) that has PostgreSQL helpers and also works with both EF Core 5 and EF Core 6.

 The full list of the “Using PostgreSQL in dev” series are:

TL;DR; – Summary of this article

  • I needed to test some code that uses PostgreSQL databases so added PostgreSQL methods to the new 5.1.0 version of my EfCore.TestSupport library, which also supports EF Core 6.
  • I show what a PostgreSQL connection string looks like and how you can access a PostgreSQL database using EF Core.
  • xUnit runs each test class in parallel, so each test class needs a unique PostgreSQL database. The new PostgreSQL methods handle this for you by adding the test class name on the end of the base database name.
  • It’s easier to write a test if the database is empty, so I show two ways to do make the empty at the start of test:
    • The standard approach is slow (e.g., took 13 seconds per test).
    • With help from the EF Core team, I added a quicker approach (e.g., took 4.2 seconds per test).

NOTE: As well as the adding PostgreSQL test helper I also reinstated the Seed from Production feature. I removed it from version 5 because I didn’t think anyone was using it, but some people  were using this and they requested I added back.

Setting the scene – my approach to testing applications using EF Core

I write a lot of EF Core code and to make sure that code is correct I use lots of automatedtesting. Over the years I have tried different ways to write and test code (mock repository, EF6’s Effort, EF Core In-Memory), but since EF Core came out I have found that testing against a real database has a lot of pros – they are:

  1. You can’t Mock EF Core’s DbContext, so you have to use an EF Core supported database in your tests.
  2. If you have a good set of database test helpers, you can write tests much quicker.
  3. Using an actual database also checks your EF Core code is correct too.

The definition of a unit test say that it doesn’t include a database, and the correct name is  integration testing. Use whatever term you like, but the main point is that the tests are run automatically and return pass/fail results. That means you can quickly check that a) the changes you have make to your code work correctly and b) the changes hasn’t broken any other parts of you’re your application.

As point 2 says, having a good set of database test helpers can significantly reduce the amount of test code you need to write. That’s why I created a library called EfCore.TestSupport, and in the recent update to support EF Core 6 I also added PostgreSQL helpers.

NOTE: If you have used EfCore.TestSupport before, then the PostgreSQL methods work the same as the SQL Server methods, but the method name contains “PostgreSql”.

How to access a PostgreSQL database

To access a PostgreSQL database, you need a connection string. This string containing the PostgreSQL information for the host, database name, and the rights to access that database. The string below shows these four parts that work with a PostgreSQL being run in Windows Linux subsystem.

“host=127.0.0.1;Database=YourDatabaseName;Username=postgres;Password=XXXXX”

IMPORTANT UPDATE

I had really poor performance when accessing the main postgres database and I have been trying to improve this. With the help of Shay Rojansky on the EF Core team I found the problem. The host should be 127.0.0.1 and not the localhost that the previous articles suggested. Changing this made a MASSIVE improvement, e.g. clearing the database used to take 13 seconds, now it takes 350 ms.! This article has been updated to show this.

NOTE: If you are running your PostgreSQL server in Windows Linux subsystem, then the host value in the connection string should be 127.0.0.1, the username should be postgres, and the password should be the same as the password you provided when running the Linux command: sudo passwd postgres.

You need to create a class that inherits EF Core’s DbContext class (see a super-simple example below). This contains information of the classes that you want to map to a database.

public class TestDbContext : DbContext
{
    public TestDbContext(DbContextOptions<TestDbContext> options)
        : base(options) { }

    public DbSet<MyEntityClass> MyEntityClasses { get; set; }
}

NOTE: You need a constructor because you are going to provide a different connection string to your tests than you provide to your main application (see this section of the EfCore.TestSupport docs on this)

To create the DbContextOptions<TestDbContext> for your xUnit test you need install the NuGet package called Npgsql.EntityFrameworkCore.PostgreSQL and build the options to pass to your DbContext. The code below shows an example of how you would manually create the options, but the EfCore.TestSupport provides methods which will do this for you (see next section).

var connectionString =  
       “host=127.0.0.1;Database=YourDatabaseName;Username=postgres;Password=XXXXX”;
var optionsBuilder = new DbContextOptionsBuilder<TContext>();
optionsBuilder.UseNpgsql(connectionString);
using var context = new TestDbContext(optionsBuilder.Options);
//… your test goes here

But there are issues when using xUnit for your testing, which makes creating the options a bit more complex, which I cover in the next section.

Using xUnit with databases

The xUnit library is the main test library used by EF Core and ASP.NET Core and recommended for .NET testing. I really like xUnit’s feature that runs each test class in parallel because running all the tests finishes much faster. The downside of xUnit running in parallel is that each test class needs its own database. That’s because having multiple tests accessing the same database in parallel is very likely to break your tests.

For this reason, EfCore.TestSupport database methods return a unique database name for each xUnit test class. It does this taking the base PostgreSqlConnection from the appsettings.json file (see code below) and adds the name of the test class to the end of the base database name. This gives you a database name unique to each test class.

{
  "ConnectionStrings": {
    "PostgreSqlConnection": "host=127.0.0.1;Database=TestSupport-Test;Username=postgres;Password=your-password",
   // The SQL Server connection string has been removed
  }
}

NOTE: EfCore-TestSupport makes sure the database in the PostgreSqlConnection connection string in the appsettings.json file ends with the word “Test”. This is useful to differentiate PostgreSQL databases that are used for testing, and therefore can be deleted without losing any application data.

The test shown below comes from the TestPostgreSqlHelpers test class tests in the EfCore.TestSupport repo. The CreatePostgreSqlUniqueDatabaseOptions extension method creates the PostgreSQL options using the this input to get the test class name to add to the database name. The options are then used to create a new DbContext.

[Fact]
public void TestPostgreSqlUniqueClassOk()
{
    //SETUP
    var options = this
          .CreatePostgreSqlUniqueClassOptions<BookContext>();
    using var context = new BookContext(options);

    // rest of the test left out
}

NOTE: The code about uses the CreatePostgreSqlUniqueDatabaseOptions extension method, but there are other versions to provide logging and method-level database uniqueness.  See the EfCore.TestSupport PostgreSQL documentation for more these methods.

Making sure a test database is empty (quickly!)

It’s possible to write tests that run correctly even if there is data already in the database, but that makes it harder to write a valid test. The best approach is to make sure the database is empty before each runs, and the simplest way is to delete the database and create a new database, as shown below.

[Fact]
public void TestEnsureDeletedEnsureCreatedOk()
{
    //SETUP
    var options = this.CreatePostgreSqlUniqueDatabaseOptions<BookContext>();
    using var context = new BookContext(options);

    context.Database.EnsureCreated();
    context.Database.EnsureCreated();

    //ATTEMPT
    context.SeedDatabaseFourBooks();

    //VERIFY
    context.Books.Count().ShouldEqual(4);
}

This is easy do and works all situations but before I found a fix it took about 13 seconds, but now its a very respectable 350 ms. Because of the initial slow 13 seconds I wasted a lot of time trying to find ways to the way improve this, but that’s how it goes in development 🙁

But it did create a version that might be of use to you called EnsureClean. Shay Rojansky, who is the PostgreSQL expert and evangelist, came back with some ideas and a PostgreSQL version of the SQL Server EnsureClean that I added in version 5.1.0 of the library.

The EnsureClean deletes all the tables, types, extensions etc. and then adds tables etc. using a version of the EnsuredCreated method. You can see this

[Fact]
public void TestEnsureCleanOk()
{
    //SETUP
    var options = this.CreatePostgreSqlUniqueDatabaseOptions<BookContext>();
    using var context = new BookContext(options);

    context.Database.EnsureClean(); 

    //ATTEMPT
    context.SeedDatabaseFourBooks();

    //VERIFY
    context.Books.Count().ShouldEqual(4);
}

EnsureClean takes about 80 ms. which is 4 times quicker than the EnsureDeleted plus EnsureCreated approach, but whether its worth using is up to you.

Conclusion

I have found automated tests is one of the best ways for delivering a good, correct code. But at the same time, I don’t want to be spend a lot of time writing tests. So, in 2017 I built a library called EfCore.TestSupport that provide methods that handles the setting up and managing test database. And as of November 2021, there has been nearly 400,000 downloads of the EfCore.TestSupport library.

Up until now the EfCore.TestSupport library supported SQL Server and Cosmos DD databases (and SQLite in-memory databases). But now that I use PostgreSQL databases, I added support for PostgreSQL databases too. My use of these methods showed me that it was worth adding some extra methods to speed up the emptying of a PostgreSQL database at the start of a test.

I was working on updating the EfCore.TestSupport library to support EF Core 6, so it was a good time to add these new PostgreSQL features too (see the EfCore.TestSupport ReleaseNotes file for the full details on the changes). I hope these new features will help you in your testing EF Core applications that use PostgreSQL databases.

Happy coding.

Using PostgreSQL in dev: Part 1 – Installing PostgreSQL on Windows

Last Updated: November 10, 2021 | Created: October 26, 2021

I started work on a library that uses a PostgreSQL, which means I need a PostgreSQL database to test it against. The best solution is to run a PostgreSQL server on my Windows development PC as that gives me a fast, free access to PostgreSQL database.

There are three good ways to run a PostgreSQL server on Windows: running a Windows version of PostgreSQL, using docker or installing PostgreSQL in Windows Subsystem for Linux, known as WSL. I used the WSL approach because it’s supposed to the quicker than the Windows version and this article described what I has to do to make PostgreSQL run properly.

I didn’t think installing PostgreSQL would be simple, and it wasn’t, and I had to do a lot of Googling to find the information I needed. The information was scattered, and some were out of date, which is why decided to write an article listing the steps to do get PostgreSQL working on Windows. The second article will then cover how to access a PostgreSQL with Entity Framework Core and unit test against a PostgreSQL database.

TL;DR; – The steps to installing PostgreSQL on Windows 10

  1. Installing WSL2 on Windows, which provides a Linux subsystem running on Windows. This was easy and painless.
  2. Installing PostgreSQL in the Linux subsystem. This was easy too.
  3. Starting / stopping the PostgreSQL software on Linux. You just have to remember about five Linux commands.
  4. Getting the Windows pgAdmin app up and running. This is where I had a problem and it took quite a while to work out what was wrong and how to fix it.
  5. Creating a PostgreSQL server via pgAdmin. Had to be careful to get the correct values set up, but once I had done that once it remembers it for next time.
  6. See the second article on how to access a PostgreSQL database from a .NET application running on Windows, and some suggestion on how to unit test against a real PostgreSQL database.

Setting the scene – why am I installing PostgreSQL

I’m a software developer, mainly working on ASP.NET Core and EF Core, and I use Windows as my development system. Up until now I have only used PostgreSQL via Azure, but I wanted a local PostgreSQL server to unit test for my library AuthPermissions.AspNetCore which will support SQL Server or PostgreSQL.

I haven’t used Linux before and I don’t really want to learn Linux unless I need to, so I wanted to find ways to use Windows commands as much as possible. Using WSL does need me to learn a few Linux commands, such as how to start / stop the PostgreSQL code and there is a PostgreSQL Linux app called psql which uses terminal commands to manage the PostgreSQL server etc. But I found a application that is like Microsoft’s SSMS (SQL Server Management Studio) application with a nice GUI front-end called pgAdmin and it even has a Windows version. That means I only need to learn a few Linux commands and manage PostgreSQL from Windows, which is great.

Here are the steps I took to install PostgreSQL using Windows Subsystem for Linux.

1. Installing WSL2 Linux (Ubuntu) on Windows 10/11

Installing WSL was easy and painless for me, and the Microsoft WSL documentation was excellent. You must be running Windows 10 version 2004 and higher (Build 19041 and higher) or Windows 11, and the command wsl –install via PowerShell or Windows Command Prompt run as administrator.

You then need to restart your PC, and after a long time the Linux subsystem will ask for a username and password. You will need the password every time you start the Linux system, so make sure you remeber it.

Couple of extra information:

2. Installing PostgreSQL on Linux

Again, I found a great Microsoft document on how to install PostgreSQL (and other databases) in your Linux Subsystem. The list below shows the things I did to install PostgreSQL.

  1. Update your Ubuntu packages: sudo apt update
  2. Install PostgreSQL (and the -contrib package which has some helpful utilities) with: sudo apt install postgresql postgresql-contrib
  3. Set the PostgreSQL password: sudo passwd postgres.
    The nice thing about that is you can change the password any time if you forget it. This password is used whenever you access PostgreSQL’s servers and databases.

TIP: Linux doesn’t use ctrl-c / ctrl-v for cut/paste. I found that I could use the normal ctril-c to cut on Windows and then if I right-clicked in Linux it would paste – that allowed me to copy commands from the docs into Linux.

3. Starting / stopping PostgreSQL on Linux

The previous Microsoft document on how to install PostgreSQL also provided the 3 commands you need once PostgreSQL is installed:

  • Checking the status of your database: sudo service postgresql status
    It says online if running, or down if stopped.
  • Start running your database: sudo service postgresql start
  • Stop running your database: sudo service postgresql stop

TIP: You don’t need to have the Linux subsystem window open to access PostgreSQL once you have started – the Linux subsystem will stay running in the background.

4. Getting the Windows pgAdmin app up and running

I found the Windows version of pgAdmin here and installed it. The pgAdmin app started OK, but I immediately had a problem of connecting to the PostgreSQL on the Linux subsystem. When I tried to create a PostgreSQL server, I got the error “password authentication failed for user “postgres” – see below

Copyright: Stack Overflow – see  this stack overflow question.

This was frustrating and took quite a while to work out what the problem is. I tried a number of things but in the end it turns out you that the authentication mode of PostgreSQL doesn’t work with the connection to Windows (see this stack overflow answer) and you need to edit PostgreSQL’s pg_hba.conf file to change what is known as the auth-method (see PostgreSQL docs on the pg_hba.conf file) from md5  to trust for the IP local connections. See code below which shows the new version – NOTE you need to scroll to the right to see the change from md5 to trust.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

The problem with the stack overflow answers I found is they were written before WSL was around, so they assume you are running PostgreSQL directly from Windows. To get to the files in the Linux subsystem I needed to use the \\wsl$ prefix to access the WSL files. In the end I found the PostgreSQL’s pg_hba.conf at \\wsl$\Ubuntu\etc\postgresql\12\main (note that the \12\ part refers to the version of PostgreSQL application).

NOTE: Editing the pg_hba.conf file needs a high level of access, but I found VSCode could edit it, or you can edit the file via NotePad run as administrator.

5. Creating a PostgreSQL server

Once the password trust problem was fixed, I tried again to create a PostgreSQL server and it worked. When the pgAdmin application is started it asks for a password, which can be anything you like (and you can change it easily). This password lets you save PostgreSQL passwords, which is useful.

To create a PostgreSQL server, I clicked the Quick Link -> Add New Server link on the main page, which then showed a Create – Server popup with various tabs. I had to fill the following tabs/field

  1. On the General tab you need to fill the Name field with your chosen name. I used PostgreServer
  2. On the Connection tab you need to set
    • Host name/address: 127.0.0.1
    • Password: the same password as given in step 2 when setting the PostgreSQL password
    • Save password: worth turning on

You now have a PostgreSQL Server, but no databases other than the system database called postgres. You can create database using pgAdmin, but in the next article I show how your application and unit tests can create databases.

NOTE: I found this article by Chloe Sun which has pictures of the pgAdmin pages, which might be useful.

Conclusion

Most of the steps were fairly easy until I hit step 4, and which point I had to do a lot of digging to find the solution. The Microsoft documents were great, but they didn’t provide all the information I needed. Thanks to all the people that answered stock overflow questions and people who write articles about this, especially Chloe Sun who wrote a similar article to this.

The next article shows you how to access a PostgreSQL database using EF Core and describes the unit test methods I added to my EfCore.TestSupport 6.0.0-preview001. These unit test helpers allow you to create a unique PostgreSQL database for each xUnit test class.

Happy coding.