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

Last Updated: October 20, 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
  7. Three ways to securely add new users to an application using the AuthP library
  8. How to take an ASP.NET Core web site “Down for maintenance”
  9. Three ways to refresh the claims of a logged-in user

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

TL;DR; – Summary of this article

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

5 2 votes
Article Rating
Subscribe
Notify of
guest
12 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Dhrumil Kothari
Dhrumil Kothari
23 days ago

Hi Jon, Great series with great documentation.

I have just one question. let me explain it via senario :

lets think that each last node(actual shop/Branch) has their own users. this users can login and able to see their orders and place the order. shop owner can see all transaction done for his shop. and top of this shop there is Branch Manager who is handling this all branches. (so its like tree of 3-4 levels, and in last node their is users associated)

So is it possible ?

Also Can we create composite key (UserID,TenantId) to identify the user for all orders/account management ?

Dhrumil Kothari
Dhrumil Kothari
21 days ago
Reply to  Jon P Smith

Thank you so much for your response. Will take a look !

I should let you know that a user is linked to one tenantId. This means if a person works in two shops then they would need two emails

Regarding this, I was thinking that, when user is authenticated, then we should ask which tenant they want to work and then this combo (userid, datakey) should apply to subsequent request. whats your thought ??

Dhrumil Kothari
Dhrumil Kothari
21 days ago

please ignore this !

Dhrumil Kothari
Dhrumil Kothari
21 days ago
Reply to  Jon P Smith

Please Ignore my last message.

I have checked Example 4. and I believe everything is perfect.

May be I was wrong in explaining the senario of multiple user.

Basically What I want is, under the each tenant there is user (Client) and who will purchase something from one/two shops. and want to see in their history.

So lets understand using your example.

Dhrumil (client on last node) , purchased the shirt from LA Shirt4U(retail outlet of 4U Inc) . and Something else from Pets2.Ltd

So, when user logged in using their cred, they should able to see both the orders (its fine if they have to switch the company using dropdown).

Raul
Raul
1 year ago

The whole serie is VERY interesting and this one is no exception! I designed a similar system for multi-tenancy using something like your DataKey. What I like is how you use features from EF to simplify quite a lot the implementation.

My implementation did not have hierarchies, but I think we will need them in the future. I am a little bit concerned about moving tenants around. I see it as a great feature if you do hierarchies, but I was thinking about the “downtime” when doing it.

I mean that such an operation should go in a transaction and it could take a LONG time. In our systems we speak about millions of records being moved around. Because of dependencies between the data, the users should not have some tables with the old tenant and some others with the new one.

How would you do it?

Thank you for the GREAT posts.

Regards,
Raul

Raul
Raul
1 year ago
Reply to  Jon P Smith

Hello Jon,
thank you very much for your answer and sorry for taking so long to answer. I think the idea of including the tenantid with the claims and not the DataKey is good. It is just sad to pay an additional database access every time for such an exceptional case (moving tenants). Or did you mean adding a User Defined Function (UDF) that will do the lookup as part of the query?

I mean like in this (pseudo) code:

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

Instead of using the DataKey we use a UDF that will be resolved at the database.

Regards,
Raul