Change table name at runtime

Question

Lets suppose that I have a db table with name Employee and a respective EF 6.0 db-first model.

Getting all rows of table Employee is done through query: context.Employees.ToList()

Is it possible, at runtime and on demand, to redirect the db table name to Test1 while using the same object name and query?

Maybe a case for EF 6.0 Interceptor usage?

Answer 1

I know it's been been a while since the original post, but I'll add my answer to help someone else. I had generic SQL queue tables with different table names. I.e. the schema is exactly the same for both tables. I created a framework so that you can dynamically poll the table of your choice by providing the name and that's why I needed to update the table name at run-time. Basically, you can create an interceptor to intercept the raw SQL queries from entity framework 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 have to get the new table name from somewhere. Either from the constructor or from a stored field in your custom DBContext which you can grab from interceptionContext.DbContexts.

Then you just have to register the 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 MyInterceptor());
    }
}

UPDATE: I found that if you add the interceptor in the constructor above will cause memory leaks. DotMemory doesn't tell you about this. Make sure you add the interceptor in a static constructor instead.

public class MyContext : DBContext
{
    public readonly string NewTableName;

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

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

Answer 2

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 would be OnModelCreating() is run once. Therefore if you use it in the same application, it'll take the first table name since it caches the result.