Change table name at runtime

问题:

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?

回答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 QueueMessageInterceptor());
    }
}

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 QueueMessageInterceptor());
    }

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

回答2:

I don't know if you should do that, but I think you can. You will have to dig into Entity Framework metadata structures, like MetadataWorkspace, which you can get from the underlying ObjectContext. See an example here: http://weblogs.asp.net/ricardoperes/entity-framework-metadata.

回答3:

Thanks for the answers.

I think that my case is a real-world scenario that is, typically neglected in all "getting-started" typical scenarios of EF tutorials and examples.

Based on the fact that I use db-first approach and the switch should be at the application level, I think that I will create a Context instance, based on different SSDL with the new table name that the user will use on demand

回答4:

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.

回答5:

Old question, but based on the problem I would suggest that you look at partioning your table based on a "current" bit or a datetime field. Partitioning is based on a column value & is supported by most modern DBMS. It would avoid issue at the ORM level.

回答6:

Why not use some good old fashioned polymorphism?

partial class Employee : IEmployee { }
partial class HistoricalEmployee : IEmployee { }

interface IEmployee {
    public string Name { get; set; }
}

void PrintEmployeeName(IEmployee employee)
{
    Debug.WriteLine(employee.Name);
}

PrintEmployeeName(context.Employees.First());
PrintEmployeeName(context.HistoricalEmployees.First());