Multitenant MVC Application using .NET Core 3.1 Global Query Filters, ModelCacheKey and Azure SQL DB

Welcome to the page that I was looking for a while ago ?.

If you want to write an MVC app in .NET Core 3.1 using Azure SQL DB and you are having multitenancy questions, we’ll try to answer them below.

To give you all a little bit of a context, this started up as an assessment from one of our clients which was requested with the thought of re-spinning an application architecture from MVC DB First Multitenant with multiple DBs to MVC DB First Multitenant with a single DB.

There are some upsides of this MVC DB First Multitenant with a single DB architecture that need to be mentioned:

• If you have a cloud-based architecture, like they had, this has a huge saving impact, money wise
• Because everything it is one DB, the management of the new features and changes is way simplified
• The authorization part of the app becomes more lightweight and easier to maintain ,and you won’t need to change between DBs every call

There are some downsides too :

• There is no segregation of data, from the tables point of view. Of course, you can use some mechanisms like Row Level Security or partitioning using a key base on which you specify the access policy, but that doesn’t come as simple as a select *
• If you want to have hybrid deployments for clients who want their own app in their own infrastructure, it might get a little bit tricky
• You must be very strict in testing your app, some of your calls now will not return an error, but another set of data, so heads up on that

When you are building an app, I would suggest starting MVC DB First Multitenant with a single DB architecture and find all the downsides you might be facing if you take this approach. If you can live them, you have your solution. 

There are 2 type of approaches of MVC DB First Multitenant with a single DB: 

• One schema 
• Per “partition” schema

To make this clear from the beginning, the architecture that we’ll be described in the next posts will accommodate a N:N relationship between the user and a tenant ( and trust me, this where the fun part was ). 

This being said, here is a list of what topics we will approach in this how-to: 

1. Modifications the Azure SQL DB structure so it can accommodate the Multitenant architecture 
2. Modifications of the JWT token generated by the application 
3. Modifications and Extensions of the DBContext class to accommodate EF Global Query Filters in a DB first project
4. Implementation of ModelCacheKey in EF Core 
5. Implementation of the Global Query Filters, Multitenant and Soft Delete, in EF Core
6. Modifications of the controllers to embed EF Core Global Query Filters functionality

Keep in mind that the examples provided are minimal and they are from a redesigned architecture from which you are seeing only the tip of the iceberg. If you have additional data and logic in your design, feel free to modify it in your best interest.

Let’s start.

Chapter 1 : Modifications of the Azure SQL DB structure so it can accommodate the Multitenant architecture