Change table name at runtime

I know it's been past since posting, but I'll add my answer to help someone else. I had shared SQL queue tables with different table names. That is, the schema is the same for both tables. I created a framework so that you can dynamically query the table of your choice by providing a name and hence I needed to update the table name at runtime. Basically, you can create an interceptor to intercept raw SQL queries from an entity and update the table name from there.

public class MyInterceptor : IDbCommandInterceptor
{
    private const string TableReplaceString = "[TheTableNameToReplace]";

    private void ReplaceTableName(DbCommand command, IEnumerable<DbContext> contexts)
    {
        var myContext = contexts?.FirstOrDefault(x => x is MyContext) as MyContext;
        if (myContext != null && command != null && command.CommandText.Contains(TableReplaceString))
        {
            command.CommandText = command.CommandText.Replace(TableReplaceString, $"[{myContext.NewTableName}]");
        }
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        ReplaceTableName(command, interceptionContext.DbContexts);
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        ReplaceTableName(command, interceptionContext.DbContexts);
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        ReplaceTableName(command, interceptionContext.DbContexts);
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        ReplaceTableName(command, interceptionContext.DbContexts);
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        ReplaceTableName(command, interceptionContext.DbContexts);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        ReplaceTableName(command, interceptionContext.DbContexts);
    }
}

      

        
        
        
      

    

Of course you need to get the new table name from somewhere. Either from the constructor or from a stored field in your custom DBC text, which you can grab from interceptionContext.DbContexts.

Then you just need to register an interceptor for your context.

public class MyContext : DBContext
{
    public readonly string NewTableName;

    public MyContext(string connectionString, string newTableName)
        : base(connectionString)
    {
        NewTableName = newTableName;
        // Set interceptor
        DbInterception.Add(new QueueMessageInterceptor());
    }
}

      

        
        
        
      

    

UPDATE: I found that if you add an interceptor to the constructor above, it will cause a memory leak. DotMemory doesn't talk about it. Make sure you add an interceptor to your static constructor.

public class MyContext : DBContext
{
    public readonly string NewTableName;

    static MyContext()
    {
        // Set interceptor only in static constructor
        DbInterception.Add(new QueueMessageInterceptor());
    }

    public MyContext(string connectionString, string newTableName)
        : base(connectionString)
    {
        NewTableName = newTableName;
    }
}

      

        
        
        
      

    

I would do something like this:

public partial class MyContext : DbContext
{
    private readonly ITableNameProvider _tableNameProvider;

    public MyContext(ITableNameProvider tableNameProvider)
        : base("name=ConnectionStringName")
    {
        _tableNameProvider = tableNameProvider;
    }

    public virtual DbSet<MyGenericEntity> Templates { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyGenericEntity>()
            .ToTable(_tableNameProvider.GetTableName(), _tableNameProvider.GetSchemaName());
    }

}

      

        
        
        
      

    

I think it works in your scenario. The only problem is the OnModelCreating () is triggered once. So if you use it in the same application, it will take the first table name as it caches the result.

Old question, but based on the problem, I suggest you take a look at splitting your table based on the "current" bit or datetime field. Partitioning is based on the value of the column and is supported by most modern DBMSs. This will avoid problems at the ORM level.

You have to create a new dbcontext that inherits from the db-first model context and treat it as code-first in ef. Check the link please. The same problem you have.

https://www.codeproject.com/Articles/421643/How-to-Use-MVC-Net-on-the-Dynamics-NAV-Database-St#_articleTop

This way, when displaying, you can get the table name dynamically.