Dynamically changing schema in Entity Framework Core
UPD here is the way I solved the problem. Although it's likely to be not the best one, it worked for me.
I have an issue with working with EF Core. I want to separate data for different companies in my project's database via schema-mechanism. My question is how I can change the schema name in runtime? I've found similar question about this issue but it's still unanswered and I have some different conditions. So I have the Resolve method that grants the db-context when necessary
public static void Resolve(IServiceCollection services) {
services.AddIdentity<ApplicationUser, IdentityRole>()
.AddEntityFrameworkStores<DomainDbContext>()
.AddDefaultTokenProviders();
services.AddTransient<IOrderProvider, OrderProvider>();
...
}
I can set the schema-name in OnModelCreating, but, as was found before, this method is called just once, so I can set schema name globaly like that
protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.HasDefaultSchema("public");
base.OnModelCreating(modelBuilder);
}
or right in the model via an attribute
[Table("order", Schema = "public")]
public class Order{...}
But how can I change the schema name on runtime? I create the context per each request, but first I fugure out the schema-name of the user via a request to a schema-shared table in the database. So what is the right way to organize that mechanism:
- Figure out the schema name by the user credentials;
- Get user-specific data from database from specific schema.
Thank you.
P.S. I use PostgreSql and this is the reason for lowecased table names.
Solution 1:
Did you already use EntityTypeConfiguration in EF6?
I think the solution would be use mapping for entities on OnModelCreating method in DbContext class, something like this:
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal;
using Microsoft.Extensions.Options;
namespace AdventureWorksAPI.Models
{
public class AdventureWorksDbContext : Microsoft.EntityFrameworkCore.DbContext
{
public AdventureWorksDbContext(IOptions<AppSettings> appSettings)
{
ConnectionString = appSettings.Value.ConnectionString;
}
public String ConnectionString { get; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(ConnectionString);
// this block forces map method invoke for each instance
var builder = new ModelBuilder(new CoreConventionSetBuilder().CreateConventionSet());
OnModelCreating(builder);
optionsBuilder.UseModel(builder.Model);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.MapProduct();
base.OnModelCreating(modelBuilder);
}
}
}
The code on OnConfiguring method forces the execution of MapProduct on each instance creation for DbContext class.
Definition of MapProduct method:
using System;
using Microsoft.EntityFrameworkCore;
namespace AdventureWorksAPI.Models
{
public static class ProductMap
{
public static ModelBuilder MapProduct(this ModelBuilder modelBuilder, String schema)
{
var entity = modelBuilder.Entity<Product>();
entity.ToTable("Product", schema);
entity.HasKey(p => new { p.ProductID });
entity.Property(p => p.ProductID).UseSqlServerIdentityColumn();
return modelBuilder;
}
}
}
As you can see above, there is a line to set schema and name for table, you can send schema name for one constructor in DbContext or something like that.
Please don't use magic strings, you can create a class with all available schemas, for example:
using System;
public class Schemas
{
public const String HumanResources = "HumanResources";
public const String Production = "Production";
public const String Sales = "Sales";
}
For create your DbContext with specific schema you can write this:
var humanResourcesDbContext = new AdventureWorksDbContext(Schemas.HumanResources);
var productionDbContext = new AdventureWorksDbContext(Schemas.Production);
Obviously you should to set schema name according schema's name parameter's value:
entity.ToTable("Product", schemaName);
Solution 2:
Sorry everybody, I should've posted my solution before, but for some reason I didn't, so here it is.
BUT
Keep in mind that anything could be wrong with the solution since it neither hasn't been reviewed by anybody nor production-proved, probably I'll get some feedback here.
In the project I used ASP .NET Core 1
About my db structure. I have 2 contexts. The first one contains information about users (including the db scheme they should address), the second one contains user-specific data.
In Startup.cs I add both contexts
public void ConfigureServices(IServiceCollection
services.AddEntityFrameworkNpgsql()
.AddDbContext<SharedDbContext>(options =>
options.UseNpgsql(Configuration["MasterConnection"]))
.AddDbContext<DomainDbContext>((serviceProvider, options) =>
options.UseNpgsql(Configuration["MasterConnection"])
.UseInternalServiceProvider(serviceProvider));
...
services.Replace(ServiceDescriptor.Singleton<IModelCacheKeyFactory, MultiTenantModelCacheKeyFactory>());
services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();
Notice UseInternalServiceProvider part, it was suggested by Nero Sule with the following explanation
At the very end of EFC 1 release cycle, the EF team decided to remove EF's services from the default service collection (AddEntityFramework().AddDbContext()), which means that the services are resolved using EF's own service provider rather than the application service provider.
To force EF to use your application's service provider instead, you need to first add EF's services together with the data provider to your service collection, and then configure DBContext to use internal service provider
Now we need MultiTenantModelCacheKeyFactory
public class MultiTenantModelCacheKeyFactory : ModelCacheKeyFactory {
private string _schemaName;
public override object Create(DbContext context) {
var dataContext = context as DomainDbContext;
if(dataContext != null) {
_schemaName = dataContext.SchemaName;
}
return new MultiTenantModelCacheKey(_schemaName, context);
}
}
where DomainDbContext is the context with user-specific data
public class MultiTenantModelCacheKey : ModelCacheKey {
private readonly string _schemaName;
public MultiTenantModelCacheKey(string schemaName, DbContext context) : base(context) {
_schemaName = schemaName;
}
public override int GetHashCode() {
return _schemaName.GetHashCode();
}
}
Also we have to slightly change the context itself to make it schema-aware:
public class DomainDbContext : IdentityDbContext<ApplicationUser> {
public readonly string SchemaName;
public DbSet<Foo> Foos{ get; set; }
public DomainDbContext(ICompanyProvider companyProvider, DbContextOptions<DomainDbContext> options)
: base(options) {
SchemaName = companyProvider.GetSchemaName();
}
protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.HasDefaultSchema(SchemaName);
base.OnModelCreating(modelBuilder);
}
}
and the shared context is strictly bound to shared schema:
public class SharedDbContext : IdentityDbContext<ApplicationUser> {
private const string SharedSchemaName = "shared";
public DbSet<Foo> Foos{ get; set; }
public SharedDbContext(DbContextOptions<SharedDbContext> options)
: base(options) {}
protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.HasDefaultSchema(SharedSchemaName);
base.OnModelCreating(modelBuilder);
}
}
ICompanyProvider is responsible for getting users schema name. And yes, I know how far from the perfect code it is.
public interface ICompanyProvider {
string GetSchemaName();
}
public class CompanyProvider : ICompanyProvider {
private readonly SharedDbContext _context;
private readonly IHttpContextAccessor _accesor;
private readonly UserManager<ApplicationUser> _userManager;
public CompanyProvider(SharedDbContext context, IHttpContextAccessor accesor, UserManager<ApplicationUser> userManager) {
_context = context;
_accesor = accesor;
_userManager = userManager;
}
public string GetSchemaName() {
Task<ApplicationUser> getUserTask = null;
Task.Run(() => {
getUserTask = _userManager.GetUserAsync(_accesor.HttpContext?.User);
}).Wait();
var user = getUserTask.Result;
if(user == null) {
return "shared";
}
return _context.Companies.Single(c => c.Id == user.CompanyId).SchemaName;
}
}
And if I haven't missed anything, that's it. Now in every request by an authenticated user the proper context will be used.
I hope it helps.
Solution 3:
Define your context and pass the schema to the constructor.
In OnModelCreating Set the default schema.
public class MyContext : DbContext , IDbContextSchema
{
private readonly string _connectionString;
public string Schema {get;}
public MyContext(string connectionString, string schema)
{
_connectionString = connectionString;
Schema = schema;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.ReplaceService<IModelCacheKeyFactory, DbSchemaAwareModelCacheKeyFactory>();
optionsBuilder.UseSqlServer(_connectionString);
}
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema(Schema);
// ... model definition ...
}
}
Implement your IModelCacheKeyFactory.
public class DbSchemaAwareModelCacheKeyFactory : IModelCacheKeyFactory
{
public object Create(DbContext context)
{
return new {
Type = context.GetType(),
Schema = context is IDbContextSchema schema
? schema.Schema
: null
};
}
}
In OnConfiguring replace the default implementation of IModelCacheKeyFactory with your custom implementation.
With the default implementation of IModelCacheKeyFactory the method OnModelCreating is executed only the first time the context is instantiated and then the result is cached. Changing the implementation you can modify how the result of OnModelCreating is cached and retrieve. Including the schema in the caching key you can get the OnModelCreating executed and cached for every different schema string passed to the context constructor.
// Get a context referring SCHEMA1
var context1 = new MyContext(connectionString, "SCHEMA1");
// Get another context referring SCHEMA2
var context2 = new MyContext(connectionString, "SCHEMA2");
Solution 4:
There are a couple ways to do this:
- Build the model externally and pass it in via
DbContextOptionsBuilder.UseModel() - Replace the
IModelCacheKeyFactoryservice with one that takes the schema into account
Solution 5:
I find this blog might be useful for you. Perfect !:)
https://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-code-first/
This blog is based on ef4, I'm not sure whether it will work fine with ef core.
public class ContactContext : DbContext
{
private ContactContext(DbConnection connection, DbCompiledModel model)
: base(connection, model, contextOwnsConnection: false)
{ }
public DbSet<Person> People { get; set; }
public DbSet<ContactInfo> ContactInfo { get; set; }
private static ConcurrentDictionary<Tuple<string, string>, DbCompiledModel> modelCache
= new ConcurrentDictionary<Tuple<string, string>, DbCompiledModel>();
/// <summary>
/// Creates a context that will access the specified tenant
/// </summary>
public static ContactContext Create(string tenantSchema, DbConnection connection)
{
var compiledModel = modelCache.GetOrAdd(
Tuple.Create(connection.ConnectionString, tenantSchema),
t =>
{
var builder = new DbModelBuilder();
builder.Conventions.Remove<IncludeMetadataConvention>();
builder.Entity<Person>().ToTable("Person", tenantSchema);
builder.Entity<ContactInfo>().ToTable("ContactInfo", tenantSchema);
var model = builder.Build(connection);
return model.Compile();
});
return new ContactContext(connection, compiledModel);
}
/// <summary>
/// Creates the database and/or tables for a new tenant
/// </summary>
public static void ProvisionTenant(string tenantSchema, DbConnection connection)
{
using (var ctx = Create(tenantSchema, connection))
{
if (!ctx.Database.Exists())
{
ctx.Database.Create();
}
else
{
var createScript = ((IObjectContextAdapter)ctx).ObjectContext.CreateDatabaseScript();
ctx.Database.ExecuteSqlCommand(createScript);
}
}
}
}
The main idea of these codes is to provide a static method to create different DbContext by different schema and cache them with certain identifiers.