Dynamically changing schema in Entity Framework Core

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");

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

There are a couple ways to do this:

• Build the model externally and pass it in via DbContextOptionsBuilder.UseModel()
• Replace the IModelCacheKeyFactory service with one that takes the schema into account

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.