Dynamically changing schema in the Entity Framework core

Question

UPD is how I solved the problem. Although this is probably not the best, it worked for me.

---

I have a problem working with EF Core. I want to share data for different companies in the database of my project using a schema mechanism. I have a question, how can I change the schema name at runtime? I found a similar question about this problem, but it still remains unanswered, and I have some other conditions. So I have a Resolve method that provides a 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 it was discovered earlier, this method is called only once, so I can set the global schema name as follows

 protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.HasDefaultSchema("public"); base.OnModelCreating(modelBuilder); } 

or directly in the model through the attribute

 [Table("order", Schema = "public")] public class Order{...} 

But how can I change the schema name at runtime? I create a context for each query, but first cross out the name of the user schema through a query to the general schema table in the database. So, how to organize this mechanism:

1. Find out the name of the schema by user credentials;
2. Get user data from a database from a specific schema.

Thanks.

PS I am using PostgreSql and this is causing low table names.

Answer 1

Have you already used EntityTypeConfiguration in EF6?

I think the solution is to use a mapping for objects in the OnModelCreating method in the 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 OnConfiguring method code forces MapProduct to execute for each instance creation for the DbContext class.

MapProduct Method Definition:

 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 for setting the schema and name for the table, you can send the schema name for one constructor to DbContext or something like that.

Do not 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 = "Production"; } 

To create a DbContext with a specific schema, you can write this:

 var humanResourcesDbContext = new AdventureWorksDbContext(Schemas.HumanResources); var productionDbContext = new AdventureWorksDbContext(Schemas.Production); 

Obviously, you should set the schema name to match the schema parameter name:

 entity.ToTable("Product", schemaName); 

Answer 2

There are several ways to do this:

• Create an external model and pass it through DbContextOptionsBuilder.UseModel()
• Replace the IModelCacheKeyFactory service IModelCacheKeyFactory one that respects the schema.

Answer 3

I think this blog might be useful to you. Fine!:)

https://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-code-first/

This blog is based on ef4, I'm not sure if it will work perfectly 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 for creating different DbContexts according to a different scheme and caching them using specific identifiers.

Answer 4

Sorry everyone, I had to publish my decision earlier, but for some reason I did not, so here it is.

BUT

Keep in mind that there may be something wrong with the solution, because it has not been verified by anyone or proven by the manufacturing process, maybe here I will get feedback.

In the project, I used ASP.NET Core 1

---

About my DB structure. I have 2 contexts. The first contains information about users (including the database schema that they should access), the second contains data related to a specific user.

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

Note the use of UseInternalServiceProvider , it was suggested by Nero Sule with the following explanation

At the very end of the EFC 1 release cycle, the EF team decided to remove the EF services from the default service collection (AddEntityFramework (). AddDbContext ()), which means that services are resolved using the native EF service provider, not the application service. provider.

To force EF to use the application service provider instead, you must first add the EF services along with the data provider to the service collection, and then configure DBContext to use the 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 a context with user 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 need to slightly change the context itself so that it takes into account the scheme:

 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 general context is strictly tied to a 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 obtaining the user schema name. And yes, I know how far this is from perfect code.

 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 did not miss anything, then this. Now, in each request of the authenticated user, the corresponding context will be used.

I hope this helps.

Answer 5

maybe i was a little late for this answer

My problem was processing another circuit with the same structure, say, multi-tenant.

When I tried to create different instances of the same context for different schemes, Entity Framework 6 starts playing, catching the first time dbContext was created, and then for the next instances they were created with a different scheme name, but onModelCreating was never called meaning that each instance points to the same pre-generated pre-generated views, pointing to the first schema.

Then I realized that creating new classes inherited from myDBContext, one for each scheme, would solve my problem by overcoming the Entity Framework catch problem, creating one new fresh context for each scheme, but then there would be a problem that we would end up with hard-coded schemes, which will cause another problem in the Code scalability conditions, when we need to add another scheme, we need to add more classes, recompile and publish a new version of the application.

So I decided to go a little further by creating, compiling and adding classes to the current solution at runtime.

Here is the code

 public static MyBaseContext CreateContext(string schema) { MyBaseContext instance = null; try { string code = $@ " namespace MyNamespace {{ using System.Collections.Generic; using System.Data.Entity; public partial class {schema}Context : MyBaseContext {{ public {schema}Context(string SCHEMA) : base(SCHEMA) {{ }} protected override void OnModelCreating(DbModelBuilder modelBuilder) {{ base.OnModelCreating(modelBuilder); }} }} }} "; CompilerParameters dynamicParams = new CompilerParameters(); Assembly currentAssembly = Assembly.GetExecutingAssembly(); dynamicParams.ReferencedAssemblies.Add(currentAssembly.Location); // Reference the current assembly from within dynamic one // Dependent Assemblies of the above will also be needed dynamicParams.ReferencedAssemblies.AddRange( (from holdAssembly in currentAssembly.GetReferencedAssemblies() select Assembly.ReflectionOnlyLoad(holdAssembly.FullName).Location).ToArray()); // Everything below here is unchanged from the previous CodeDomProvider dynamicLoad = CodeDomProvider.CreateProvider("C#"); CompilerResults dynamicResults = dynamicLoad.CompileAssemblyFromSource(dynamicParams, code); if (!dynamicResults.Errors.HasErrors) { Type myDynamicType = dynamicResults.CompiledAssembly.GetType($"MyNamespace.{schema}Context"); Object[] args = { schema }; instance = (MyBaseContext)Activator.CreateInstance(myDynamicType, args); } else { Console.WriteLine("Failed to load dynamic assembly" + dynamicResults.Errors[0].ErrorText); } } catch (Exception ex) { string message = ex.Message; } return instance; } 

I hope this helps someone save time.

Answer 6

You can use the Table attribute in tables in a fixed schema.

You cannot use the attribute for schema change tables, and you need to configure this through the free ToTable API.
If you disable the model’s cache (or write your own cache), the scheme may change with each request, so when creating a context (each time), you can specify the scheme.

This is the main idea.

 class MyContext : DbContext { public string Schema { get; private set; } public MyContext(string schema) : base() { } // Your DbSets here DbSet<Emp> Emps { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Emp>() .ToTable("Emps", Schema); } } 

You can now have different ways to determine the schema name before creating the context.
For example, you can have your “system tables” in a different context, so with each request you get the schema name from the user name using the system tables and create a working context in the correct schema (you can share tables between contexts).
You can disconnect system tables from the context and use ADO.Net to access them.
There may be a few more solutions.

You can also look here.
Multi-Tenant Code First EF6

and you can google ef multi tenant

EDIT
There is also a model caching problem (I forgot about that). You must disable model caching or change cache behavior.

Answer 7

Update for MVC Core 2.1

You can create a model from a database with several schemas. The system is slightly independent of the scheme in its names. Tables with the same name are added to "1". "dbo" is the intended schema, so you don't add anything by adding a table name prefix to it using the PM command

You will have to rename the model and class file names yourself.

In the PM console

 Scaffold-DbContext "Data Source=localhost;Initial Catalog=YourDatabase;Integrated Security=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -force -Tables TableA, Schema1.TableA