Database Schema not changing at Runtime in Asp.net Core 2.2 & Entity Framework Core

Question

i had an application where data is saved in different sql schema for different Users.

For e.g.

User 1 Data is saved in SCHEMA1

User 2 Data is saved in SCHEMA2

Previously application was developed in MVC 3 and it is working fine and as expected. Now we are migrating application in .Net Core 2.2 in which this fucntionality is not working
.net core does not have IDbModelCacheKeyProvider due to this only one schema is working

Below is the DBContext File

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    //public string Schema { get; set; }
    private readonly IConfiguration configuration;
    public string SchemaName { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {

    }

    public ApplicationDbContext(string schemaname)
        : base()
    {
        SchemaName = schemaname;
    }

    public DbSet<EmployeeDetail> EmployeeDetail { get; set; }



    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory())
                      .AddJsonFile("appsettings.json");
        var configuration = builder.Build();

        optionsBuilder.UseSqlServer(configuration["ConnectionStrings:SchemaDBConnection"]);

        var serviceProvider = new ServiceCollection().AddEntityFrameworkSqlServer()
                            .AddTransient<IModelCustomizer, SchemaContextCustomize>()
                            .BuildServiceProvider();
    }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.RemovePluralizingTableNameConvention();
        modelBuilder.HasDefaultSchema(SchemaName);
        base.OnModelCreating(modelBuilder);
    }

    public string CacheKey
    {
        get { return SchemaName; }
    }


}   


public class SchemaContextCustomize : ModelCustomizer
{
    public SchemaContextCustomize(ModelCustomizerDependencies dependencies)
        : base(dependencies)
    {

    }
    public override void Customize(ModelBuilder modelBuilder, DbContext dbContext)
    {
        base.Customize(modelBuilder, dbContext);

        string schemaName = (dbContext as ApplicationDbContext).SchemaName;
        (dbContext as ApplicationDbContext).SchemaName = schemaName;

    }
}

My question is how to change schemaName at runtime

So what is the right way to organize that mechanism:

Figure out the schema name by the user credentials; Get user-specific data from database from specific schema.

IDbModelCacheKeyProvider is not about schemas. What you used in MVC 3 was a workaround, not the proper way to use different schemas with the same contexts — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2019 at 10:54
@PanagiotisKanavos thanks for information, i had managed to solve the issue using custom UserManager and below context class. If possible can you just share example or URL for implementing different schemas with the same contexts, it would be helpful — Nilesh Gajare
– Nilesh Gajare, Commented Jun 21, 2019 at 11:03

Nilesh Gajare · Accepted Answer · 2019-06-26 09:44:47Z

I was able to change schema at runtime by changing onConfiguring Method

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{

    public string SchemaName { get; set; }


    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {

    }

    public ApplicationDbContext(string schemaname)
        : base()
    {
        SchemaName = schemaname;
    }

    public DbSet<EmployeeDetail> EmployeeDetail { get; set; }



    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {

        var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory())
                      .AddJsonFile("appsettings.json");
        var configuration = builder.Build();

        var serviceProvider = new ServiceCollection().AddEntityFrameworkSqlServer()
                            .AddSingleton<IModelCustomizer, SchemaContextCustomize>()
                            .BuildServiceProvider();
        optionsBuilder.UseSqlServer(configuration["ConnectionStrings:SchemaDBConnection"]).UseInternalServiceProvider(serviceProvider);
    }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
       // modelBuilder.MapProduct(SchemaName);

        modelBuilder.RemovePluralizingTableNameConvention();
        if (!string.IsNullOrEmpty(SchemaName))
        {
            modelBuilder.HasDefaultSchema(SchemaName);
        }
        base.OnModelCreating(modelBuilder);
    }

    public string CacheKey
    {
        get { return SchemaName; }
    }
public class SchemaContextCustomize : ModelCustomizer
{
    public SchemaContextCustomize(ModelCustomizerDependencies dependencies)
        : base(dependencies)
    {

    }
    public override void Customize(ModelBuilder modelBuilder, DbContext dbContext)
    {
        base.Customize(modelBuilder, dbContext);

        string schemaName = (dbContext as ApplicationDbContext).SchemaName;
        (dbContext as ApplicationDbContext).SchemaName = schemaName;

    }
}
}

Hi. I know this is almost a year old, but I don't understand how are did the change you implemented has helped you. The only (important) difference I can see is that you changed timespan of SchemaContextCustomize from Transient to Singleton. is CacheKey property even used? How are you dealing with migrations (assuming you use them)?

Alaaeddine HFIDHI · Accepted Answer · 2019-06-20 14:20:33Z

0

The best way is to use Multi-Tenancy Architecture to be able to use database schema per user ( Tenant )
This architecture is recommended for Saas appllications

Concepts

Let’s agree on some basic concepts:

We use a tenant identification (or resolution) strategy to find out what tenant are we talking to
A tenant DbContext access strategy will figure out the way to retrieve (and store)

This article will show you how to implement Multi-tenancy app using .net core : https://stackify.com/writing-multitenant-asp-net-core-applications/

answered Jun 20, 2019 at 14:20

Alaaeddine HFIDHI

8864 silver badges12 bronze badges

3 Comments

Panagiotis Kanavos Over a year ago

This has little to do with the question. The OP asks how to change the schema used by the context, to connect to the correct tenant's schema

Alaaeddine HFIDHI Over a year ago

In his question he said :" So what is the right way to organize that mechanism:" , I tried to explain the concepts and the philosophy instead of give him a bit of codes ...

Panagiotis Kanavos Over a year ago

The OP already uses a multitenant architecture with multiple schemas. The only thing that could help is the link to an article that explains multiple different techniques, only one of them related to multiple schemas. A helpful answer would explain what to do and why and include the relevant code.

Collectives™ on Stack Overflow

Database Schema not changing at Runtime in Asp.net Core 2.2 & Entity Framework Core

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related