EF multi tenant instance: fast implementation of sub database and sub table

Preface

In this essay, we continue to demonstrate how to implement EF multi tenancy.

Today, we mainly demonstrate the transformation under multi tenancy, as shown in the following figure

Implementation

Project structure

This time, our sample project is streamlined, with only one API project, directly containing all the code.

As like as two peas, Controller, StoreContext and Entity are all the same.

The main differences are the combined connection generator and Startup

Code interpretation

1. The first thing to focus on is whether it is a Startup or a routine. Register EF multi tenant in ConfigureService and Configure Middleware in ConfigureService.

ConfigureService is always simple. But notice the addmysql pertable pattern used here.

In a mixed pattern, the smallest unit is required to register as a service. Because this is a database and data table mixed mode, you need to use data tables to register.

1 public void ConfigureServices(IServiceCollection services)
2 {
3     services.AddScoped<IConnectionGenerator, CombindedConnectionGenerator>();
4     services.AddMySqlPerTable<StoreDbContext>(settings =>
5     {
6         settings.ConnectionPrefix = "mysql_";
7     });
8     services.AddControllers();
9 }

Configuration is easier to use. You only need to add the middleware tenantinfo middleware.

 1 public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
 2 {
 3     if (env.IsDevelopment())
 4     {
 5         app.UseDeveloperExceptionPage();
 6     }
 7 
 8     app.UseMiddleware<TenantInfoMiddleware>();
 9 
10     app.UseRouting();
11 
12     app.UseEndpoints(endpoints =>
13     {
14         endpoints.MapControllers();
15     });
16 }

2. You need to implement the ConnectionGenerator yourself this time

There are two key points,

The first key point is that our class library supports multiple dbcontexts at the same time, so we need a TenantKey to distinguish. Because there is a special case that requires a ConnectionGenerator to support multiple dbcontexts at the same time, the MatchTenantKey method is provided as a supplementary judgment basis.

It can be seen that TenantKey here is empty, so it will not match normally. In the example, MatchTenantKey is completely relied on for matching.

The second key point, GetConnection, as the most important logical method, is to take the module of the digital part of TenantName and finally the key value of ConnectionString at the splicing place

And get the connection string through Configuration

 1 public class CombindedConnectionGenerator : IConnectionGenerator
 2 {
 3     private readonly IConfiguration configuration;
 4     public string TenantKey => "";
 5 
 6     public CombindedConnectionGenerator(IConfiguration configuration)
 7     {
 8         this.configuration = configuration;
 9     }
10 
11 
12     public string GetConnection(TenantOption option, TenantInfo tenantInfo)
13     {
14         var span = tenantInfo.Name.AsSpan();
15         if (span.Length > 4 && int.TryParse(span[5].ToString(), out var number))
16         {
17             return configuration.GetConnectionString($"{option.ConnectionPrefix}container{number % 2 + 1}");
18         }
19         throw new NotSupportedException("tenant invalid");
20     }
21 
22     public bool MatchTenantKey(string tenantKey)
23     {
24         return true;
25     }
26 }

Test result

I don't think it's necessary to test the results. They are the same routine. The main difference is that there was only one database or multiple databases before

This hybrid mode mainly uses a database as a container, which can contain multiple product data tables at the same time.

Container1