entity framework - 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;
    }
}