How to change database schema on runtime in EF7 or EF core

Question

My database have different schema depending on user selections on 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);
    }
}

Upon testing. I created a context instance with 'schema1'. So far so good.

But when I create another context instance with different schema 'schema2', the resulting data in which the schema is still on '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 the OnModelCreating is called only one time, so it is never called again when you create a new context instance of the same connection string.

Is it possible to have dynamic schema on runtime? Note: this is possible in EF6

Answer 1

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

You ought to override default 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 Equals and GetHashCode overridden methods, they are not best one 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 in DI.

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

Context sample.

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

Answer 2

You can build the model externally and pass it into the DbContext using DbContextOptionsBuilder.UseModel()

Another (more advanced) alternative is to replace the IModelCacheKeyFactory to take schema into account.

Answer 3

I found a way to recreate the compiled model on each context creation.

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();
    }
}