In my previous article, I wrote about how you could handle multitenancy on the level of your databases.
To keep this topic going inside the frame of ASP.NET Core web applications, I’d like to explain how we can leverage the various multitenancy configurations inside EF Core by leveraging a new EF Core 3 feature called Interception of Database operations.
This feature allows us to hook into the raw SQL commands being generated before and after the database was called. It also allows us to modify the results after they’re retrieved, to do some sort of filtering, for example.
In this article, we’ll look how we can use the IDbCommandInterceptor to handle multitenancy in EF Core.
I’ve created a GitHub repository to accompany this article.
You can't perform that action at this time. You signed in with another tab or window. You signed out in another tab or…
In this repo you’ll find the database scripts to setup our example environments, you can find them here.
After creating the databases and cloning the repository, you can play around with the various multitenancy configurations in the Startup.cs file:
First, let’s look into the Discriminator column.
Also known as the discriminator column
In EF Core 3, you can register an Microsoft.EntityFrameworkCore.Diagnostics.IInterceptor. This allows you to hook into the command execution of EF Core.
You can do this via the AddInterceptors extension on DbContextOptionsBuilder, but this requires concrete instances of our interceptors and can’t leverage the dependency injection from .NET Core.
To use dependency injection, EF Core needs to have it’s own ServiceProvider from which it can resolve these interceptors. For our usecase we require that the tenant is provided via a HTTP Header in the request.
For this reason, we need DI to inject an instance of TenantInfo.
This is a simple context-object that lets us know what the current tenant context is. It will be set as Scoped in the pipeline, meaning it is created per HTTP request (in ASP.NET Core). A middleware will read the HTTP Tenant header from the current request and modify this object’s Name property to match whatever value the client has sent.
When we request a new DbContext, we’ll need to pass in the TenantInfo from the parent context (ASP.NET Core servicecollection) into the dedicated servicecollection for EF. This is done via this helper method.
The servicecollection from the parent is passed in, along with the configuration. When a new DbContextOptions is requested, it first creates a new ServiceCollection and adds EntityFramework, adds the TenantInfo by retrieving it from the parent servicecollection and lastly, adds the IInterceptor of choice.
This makes registering an interceptor fairly simple:
public static IServiceCollection UseDiscriminatorColumn(this IServiceCollection services, IConfiguration configuration)=> services.UseEFInterceptor<DiscriminatorColumnInterceptor>(configuration);
This is the whole ServiceCollectionExtensions class:
Moving onto the interceptor, using a EF Core DbCommandInterceptor, we can modify the SQL Query to add a WHERE statement at the end of the query to filter on the discriminator column called ‘Tenant’.
You can see here that we first target the DiscriminatorDB, this is required because our connection string does not contain a default catalog.
Secondly, based on whether a WHERE clause was already present, because a .Where() Linq filter was added to a Select, perhaps. We either add an AND clause or just append a WHERE clause at the end of the query.
This is by no means bulletproof, but it gets the job done for our simple scenario.
If we launch the application, open up Postman, we should see two different data sets when we apply a different HTTP header value for Tenant.
Results for Dell:
Results for HP:
Notice that the IDs match whatever is inside the DiscriminatorDB database:
Since we’ve already handled most of the boilerplate in the section above, I’ll briefly discuss the SchemaInterceptor here.
Here we can see, that again, the catalog is chosen (TenantPerSchemaDb). Secondly, the FROM and JOIN statements are prepended with a schema based on the TenantInfo.
I must repeat, this is by no means failsafe. You’re better off parsing the SQL using a genuine parser and modifying the SQL using an expression tree, for example. This way, you’re sure that whatever SQL is created, won’t cause issues when executed.
The DatabaseInterceptor is even simpler, it basically just adds a Use Database statement at the beginning of the command, indicating whichever tenant database is requested.
Now, we exit the land of the interceptors, this need can easily be met by using a different connection whenever the DbContext is created.
We’ve looked into what EF Core Interceptors can bring to the table, but we merely scratched the surface. There’s lots more use-cases for this golden feature, I can’t wait to explore it, and the rest of what .NET Core 3 has to offer!
Later today we discovered that the foreign key naming conventions have changed, in contrast to EF Core 2.2. Make sure to check out the breaking changes in the EF Core 3 release before migrating any existing codebase ✌️