Change table name at runtime

entity-framework entity-framework-6

Popular Answer

Although the initial post was made a while ago, I'll still add my response to aid someone else. I had many table names for generic SQL queue tables. In other words, both tables have exactly the same schema. It was necessary for me to change the table name at run-time since I built a framework that allows you to dynamically poll the table of your choosing by supplying the name. In essence, you can build an interceptor to catch the entity framework's raw SQL queries and change 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);
    }
}

Naturally, you must obtain the new table name from some source. Either from the constructor or from a stored field in your unique DBContext, which you can take use of by grabbing it from interceptionContext.DbContexts.

The interceptor must then be registered 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 discovered that including the interceptor in the aforementioned constructor will result in memory leaks. You are not informed about this by DotMemory. Instead, make sure to include the interceptor in a static constructor.

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;
    }
}
5
1/25/2020 6:39:27 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow