How to change the database schema at run time in an EF7 or EF core

Question

My database has different schemes depending on the user's choice at runtime.

My code is below:

public partial class FashionContext : DbContext { private string _schema; public FashionContext(string schema) : base() { _schema = schema; } public virtual DbSet<Style> Styles { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder options) { options.UseSqlServer(@"Server=.\sqlexpress;Database=inforfashionplm;Trusted_Connection=True;"); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Style>() .ToTable("Style", schema: _schema); } } 

After testing. I created a context instance using "schema1". So far, so good.

But when I create another instance of the context with another schema "schema2", the resulting data in which the schema is still in "schema1".

Here is the implementation:

 using (var db = new FashionContext("schema1")) { foreach (var style in db.Styles) { Console.WriteLine(style.Name); } } Console.ReadLine(); Console.Clear(); using (var db = new FashionContext("schema2")) { foreach (var style in db.Styles) { Console.WriteLine(style.Name); } } Console.ReadLine(); 

Later, I noticed that OnModelCreating is called only once, so it is never called again when you create a new instance of the context of the same connection string.

Is it possible to have a dynamic circuit at runtime? Note: this is possible in EF6

Answer 1

You can build the model from the outside and pass it to DbContext using DbContextOptionsBuilder.UseModel()

Another (more advanced) option is to replace IModelCacheKeyFactory with the adoption of the schema.

Answer 2

One of the possible ways was mentioned above, but briefly, so I will try to explain with examples.

You must override the standard ModelCacheKeyFactory and ModelCacheKey.

ModelCachekeyFactory.cs

 internal sealed class CustomModelCacheKeyFactory<TContext> : ModelCacheKeyFactory where TContext : TenantDbContext<TContext> { public override object Create(DbContext context) { return new CustomModelCacheKey<TContext>(context); } public CustomModelCacheKeyFactory([NotNull] ModelCacheKeyFactoryDependencies dependencies) : base(dependencies) { } } 

ModelCacheKey.cs, please review the overridden Equals and GetHashCode methods, they are not the best and should be improved.

 internal sealed class ModelCacheKey<TContext> : ModelCacheKey where TContext : TenantDbContext<TContext> { private readonly string _schema; public ModelCacheKey(DbContext context) : base(context) { _schema = (context as TContext)?.Schema; } protected override bool Equals(ModelCacheKey other) { return base.Equals(other) && (other as ModelCacheKey<TContext>)?._schema == _schema; } public override int GetHashCode() { var hashCode = base.GetHashCode(); if (_schema != null) { hashCode ^= _schema.GetHashCode(); } return hashCode; } } 

Register at DI.

 builder.UseSqlServer(dbConfiguration.Connection) .ReplaceService<IModelCacheKeyFactory, CustomModelCacheKeyFactory<CustomContext>>(); 

Context example.

 public sealed class CustomContext : TenantDbContext<CustomContext> { public CustomContext(DbContextOptions<CustomContext> options, string schema) : base(options, schema) { } } 

Answer 3

I found a way to recreate the compiled model each time I create a context.

 public partial class MyModel : DbContext { private static DbConnection _connection { get { //return a new db connection } } private static DbCompiledModel _model { get { return CreateModel("schema name"); } } public MyModel() : base(_connection, _model, false) { } private static DbCompiledModel CreateModel(string schema) { var modelBuilder = new DbModelBuilder(); modelBuilder.HasDefaultSchema(schema); modelBuilder.Entity<entity1>().ToTable(schema + ".entity1"); var builtModel = modelBuilder.Build(_connection); return builtModel.Compile(); } }