ef core new dbcontext executes OnModelCreating only once

Question

we have regularly changing Database Data(every two weeks to once a month). Usually the latest data has to be used, but in some special cases older data is necessary.

The current info which version has to be used atm is stored in another table.

The Database looks like this, versioned Schema-Names with the same tables beneath it.

YYYYMMDD+Revision

myshema_202001011
    table1
myshema_202002011 and so on
    table1
myshema_202003011 and so on
    table1

I have build a Aspnet core (2.2) service with two DbContext classes, one for the static schemas that gets the current version to use and one for the changing schemas that accesses those data.

The static DbContext works just fine.

The problem is, even when i use the changing contaxt with a using like,

using (var _context = new ChangingDbContext()){}

the constructors and OnConfiguring are executed each time but the OnModelCreating method is only executed once. This leads to NOT updating to the current schemas.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.HasAnnotation("ProductVersion", "2.2.6-servicing-10079");

    modelBuilder.Entity<my_table>(entity =>
    {
        entity.HasKey(e => e.key_adr);
        entity.ToTable("mytable", $"myshema{mySchemaVersion}");
    }); 
}

Has anyone a clue how to get a "really" new context where OnModelCreating is executed every time? Or maybe another solution how to handle those changing Schemas?

Just to confirm. You are saying your DB changes every 2 weeks. And you need to change your DBContext code every 2 weeks? — Alex - Tin Le, Mar 06 '20 at 08:14
Yes kinda. Its easier then blowing up the original tables we get with some revison logic. — Mr.Pearce, Mar 06 '20 at 08:41
Well, this is the first time I hear this. I think you can do dynamic columns for tables. I'll explain more in the answer. — Alex - Tin Le, Mar 06 '20 at 09:23
See https://stackoverflow.com/questions/51864015/entity-framework-map-model-class-to-table-at-run-time/51899590#51899590. Also https://learn.microsoft.com/en-us/ef/core/modeling/dynamic-model — Ivan Stoev, Mar 06 '20 at 12:25

Mr.Pearce · Answer 1 · 2020-03-06T13:34:59.977

Solved by this Answer https://stackoverflow.com/a/41985226/6692289

Quote from Example in case it gets deleted.

Derived DbContext that replaces it's ModelCacheKey (and factory) with a Custom one.

class MyDbContext : DbContext
{
    public MyDbContext(string schema)
    {
        Schema = schema;
    }

    public string Schema { get; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
            .UseSqlServer("...")
            .ReplaceService<IModelCacheKeyFactory, MyModelCacheKeyFactory>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(Schema);

        // ...
    }
}

The factory that creates the Context with a specific key.

class MyModelCacheKeyFactory : IModelCacheKeyFactory
{
    public object Create(DbContext context)
        => new MyModelCacheKey(context);
}

The custom ModelCacheKey per context.

class MyModelCacheKey : ModelCacheKey
{
    string _schema;

    public MyModelCacheKey(DbContext context)
        : base(context)
    {
        _schema = (context as MyDbContext)?.Schema;
    }

    protected override bool Equals(ModelCacheKey other)
        => base.Equals(other)
            && (other as MyModelCacheKey)?._schema == _schema;

    public override int GetHashCode()
    {
        var hashCode = base.GetHashCode() * 397;
        if (_schema != null)
        {
            hashCode ^= _schema.GetHashCode();
        }

        return hashCode;
    }
}

And using the Context like.

using (var _myContext = new MyDbContext(_schemaNameToUse)
{
}

score 0 · Answer 2 · answered Mar 06 '20 at 09:27

0

To continue from my comment. The below db table design allows you or users add as many as new fields to an object as they want. And I think it gives most flexible structure.

Let's assume in a eCommerce system, we provide 3 fields (Name, Code, Price) for the product. But we also allow users want to add their custom fields to their products (e.g. Promotion1Price, Promotion2Price, Discount, ...)

PRODUCT (ProductId, Name, Code, Price)

CUSTOMEFIELD (FieldId, FieldName, FieldType)

PRODUCT_CUSTOMFIELD (ProductId, FieldId, FieldValue)

Let me know if this doesn't serve your purpose right.

answered Mar 06 '20 at 09:27

Alex - Tin Le

1,982
1
6
11

The tables itself are not really the problem. Table alterations(new columns and stuff) are known month in advance so they can be easily be handled long before they are even relevant. The only thing that blocks me is that OnModelCreating only runs once and therefore the Table schema for my context wont change. I have a Method that Sets up the schema names for the DbContext but when the OnModelCreating is only runs once it wont get updated. – Mr.Pearce Mar 06 '20 at 09:42
What I try to say is. Your root issue is table needs to change frequently. Then the correct solution is to design tables which allow to change frequently. Your solution is to design a code to change frequently. And I don't think it's a good design. Because I don't see any value of converting the problem of changing table to changing code. Hope it makes sense. – Alex - Tin Le Mar 06 '20 at 09:45

ef core new dbcontext executes OnModelCreating only once

2 Answers2