efcore 多租户动态设置查询schema

想象一下，你有一个多租户的系统，数据分别存储在不同的schema中，现在用户需要指定schema，从不同的schema中查询数据。则可按照如下进行

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata.Builders;


namespace Multi_Tenant_Dynamic_Schema.Models
{
    public class Test
    {
        public int ID { get; set; }

        public string Name { get; set; }
    }

    public class TestConfig : IEntityTypeConfiguration<Test>
    {
        public void Configure(EntityTypeBuilder<Test> builder)
        {
            builder.ToTable("test");
            builder.HasKey(t => t.ID);
            builder.Property(t => t.ID).HasColumnName("id");
            builder.Property(t=>t.Name).HasColumnName("name");
        }
    }


    public class CustomDbContext : DbContext
    {
        public string _schema { get; set; }
        public CustomDbContext(DbContextOptions<CustomDbContext> options)
            : base(options)
        {
        }

        public DbSet<Test> Tests { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema(_schema);
            modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);

            base.OnModelCreating(modelBuilder);
        }

    }



    public class DbSchemaAwareModelCacheKeyFactory : IModelCacheKeyFactory
    {

        public object Create(DbContext context)
        {
            return new
            {
                Type = context.GetType(),
                Schema = context is CustomDbContext schema
                    ? schema._schema
                    : null
            };
        }
    }
}

其中我们在OnModelCreating中指定了schema，需要注意的是OnModelCreating只会执行一次，因为为了提高performance，efcore对数据进行了缓存，我们需要重写IModelCacheKeyFactory

DI

builder.Services.AddHttpContextAccessor();

builder.Services.AddDbContext<CustomDbContext>((serviceProvider, options )=>
{
    options.UseNpgsql(@"Host=192.168.214.133;Port=32222;Database=postgresdb;Username=postgresadmin;Password=admin123")
    .LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information)
                   .EnableSensitiveDataLogging()
                   .ReplaceService<IModelCacheKeyFactory, DbSchemaAwareModelCacheKeyFactory>(); ; // Optional: Log sensitive data such as parameter values
    

    //var schema = serviceProvider.GetRequiredService<IHttpContextAccessor>()?.HttpContext?.Request.Headers["Schema"].ToString() ?? "public";

});

使用

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Multi_Tenant_Dynamic_Schema.Models;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace Multi_Tenant_Dynamic_Schema.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ValuesController : ControllerBase
    {
        
        private readonly CustomDbContext  _customDbContext;

        public ValuesController(CustomDbContext customDbContext)
        {
            _customDbContext = customDbContext;
        }


        // GET api/<ValuesController>/5
        [HttpGet]
        public async Task<List<Test>> Get(string schema)
        {
            _customDbContext._schema = schema;
            return  await _customDbContext.Tests.ToListAsync();
        }

        
    }
}