Dynamically changing schema in Entity Framework Core

Question

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:

1. Figure out the schema name by the user credentials;
2. 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.

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