C#多租户-EF Core动态链接

场景

Sass产品一般都要考虑租户中的数据隔离，在数据库层面一般分为几种做法：

1. 独立数据库
2. 独立的表
3. 表中加入租户标识

本文讲解使用EF Core时，如何在一个服务根据租户身份使用不同的数据库（独立数据库）

实现

在AddDbContext的时候，使用提前注入好实现的的ISQLStrResolver服务的GetConnectionString方法动态获取链接字符串（我这里还有一个TenantInfo包含数据库类型的字段）

services.AddDbContext<TDbContect>((serviceProvider, options) =>
{
    var tenantInfo = serviceProvider.GetRequiredService<TenantInfo>();
    var sqlStrResolver = serviceProvider.GetRequiredService<ISQLStrResolver>();
    if(tenantInfo.CddDataBaseType == CddDataBaseTypeEnumPCM.SQLServer)
    {
        options.UseSqlServer(sqlStrResolver.GetConnectionString());
    }
    else
    {
        options.UseNpgsql(sqlStrResolver.GetConnectionString());
    }
});

ISQLStrResolver实现（因为多租户数据库连接是从中间件中请求租户中心获取的，测试环境下不想这么麻烦，所以会使用本地的连接字符串）

public SQLStrResolver(TenantInfo tenantInfo, IConfiguration configuration,
    IWebHostEnvironment env, ILogger<SQLConnectionStrResolver> logger)
{
    _tenantInfo = tenantInfo;
    _configuration = configuration;
    _env = env;
}

public string GetConnectionString()
{
    var connectString = "";
    // 只有本地运行环境允许这样
    if (_env.IsDevelopment())
    {
        connectString = _configuration.GetConnectionString("DbContext");
    }
    else if (!string.IsNullOrWhiteSpace(_tenantInfo.ConnectionString))
    {
        connectString = _tenantInfo.ConnectionString;
    }
    return connectString;
}

多租户中间件的实现（简略实现）：

public class TenantMiddleware : IMiddleware
{
    private readonly IWebHostEnvironment _env;
    private readonly IConfiguration _configuration;
    private readonly ILogger<TenantMiddleware> _logger;
    private readonly IMemoryCache _memoryCache;

    public TenantMiddleware(IWebHostEnvironment env,
        IConfiguration configuration, ILogger<TenantMiddleware> logger,
        IMemoryCache memoryCache)
    {
        _env = env;
        _configuration = configuration;
        _logger = logger;
        _memoryCache = memoryCache;
    }


    public async Task InvokeAsync(HttpContext context, RequestDelegate next)
    {
        var tenantInfo = context.RequestServices.GetRequiredService<TenantInfo>();
        var serverRunTimeParameter = context.RequestServices.GetRequiredService<ServerRunTimeParameterPCM>();
        var tenantIdentifier = context.GetTenantIdentifier();
        tenantInfo.TenantIdentifier = context.GetTenantIdentifier();
       
        if (!string.IsNullOrWhiteSpace(tenantIdentifier))
        {
            // use MemoryCache
            var cacheKey = tenantIdentifier + tenantInfo.ServerIdentifier + tenantInfo.ClientIdentifier;
            var runTimeParameter = _memoryCache.Get<ServerRunTimeParameterPCM>(cacheKey);

            if(runTimeParameter == null)
            {
                var res = await TenantServerPCRequest.GetServerRunTimeParameter(serverIdentifier, context.GetNeeds());
                if (!res.IsSuccessCode)
                {
                    _logger.LogError(res.Message);
                    context.Response.StatusCode = 500;
                    return;
                }
                runTimeParameter = res.PCMModel;
                var copyedData = JsonConvert.DeserializeObject<ServerRunTimeParameterPCM>(JsonConvert.SerializeObject(runTimeParameter));
                _memoryCache.Set(cacheKey, copyedData, TimeSpan.FromMinutes(30));
            }
            try
            {
		// 填充数据库类型和数据库连接字符串
                tenantInfo.ConnectionString = DecryptHelper.Decrypt(runTimeParameter.SQLConnectionString);
                tenantInfo.CddDataBaseType = runTimeParameter.CddDataBaseType;
                if (_env.IsDevelopment())
                {
                    var dbType = _configuration.GetValue<string>("DataBaseType");
                    if (string.IsNullOrWhiteSpace(dbType))
                    {
                        tenantInfo.CddDataBaseType = CddDataBaseTypeEnumPCM.SQLServer;
                    }
                    else if (dbType == "SQLServer")
                    {
                        tenantInfo.CddDataBaseType = CddDataBaseTypeEnumPCM.SQLServer;
                    }
                    else if (dbType == "PostgreSQL")
                    {
                        tenantInfo.CddDataBaseType = CddDataBaseTypeEnumPCM.PostgreSQL;
                    }
                }
            }
           
            catch (Exception ex)
            {
                context.Response.StatusCode = 400;
                return;
            }
            await next(context);
        }
        else
        {
            _logger.LogError("TenantIdentifier Or ClientIdentifier Was No Found");
            context.Response.StatusCode = 400;
        }
    }
}

其实就是经过中间件，将所需的租户使用的数据库资源连接字符串请求过来，之后在每次使用DbContext的时候，会先经过

    var tenantInfo = serviceProvider.GetRequiredService<TenantInfo>();
    var sqlStrResolver = serviceProvider.GetRequiredService<ISQLStrResolver>();
    if(tenantInfo.CddDataBaseType == CddDataBaseTypeEnumPCM.SQLServer)
    {
        options.UseSqlServer(sqlStrResolver.GetConnectionString());
    }
    else
    {
        options.UseNpgsql(sqlStrResolver.GetConnectionString());
    }

数据库连接字符串就被动态填充进来了。