想象一下,你有一个多租户的系统,数据分别存储在不同的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();
}
}
}