在运行时更改表名称 [英] Change table name at runtime

问题描述

让我们假设我有一个名为 Employee 的数据库表和一个相应的EF 6.0 db-first模型.

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

通过查询完成获取表 Employee 的所有行:context.Employees.ToList()

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

是否可以在运行时按需将数据库表名重定向到 Test1 ，同时使用相同的对象名和查询?

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

也许可以使用EF 6.0拦截器?

Maybe a case for EF 6.0 Interceptor usage?

推荐答案

我知道距原始帖子已经有一段时间了，但是我将添加答案来帮助其他人.我有具有不同表名的通用SQL队列表. IE.两个表的架构完全相同.我创建了一个框架，以便您可以通过提供名称来动态轮询您选择的表，这就是为什么我需要在运行时更新表名的原因.基本上，您可以创建一个拦截器来拦截来自实体框架的原始SQL查询，并从那里更新表名.

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

当然，您必须从某个地方获取新的表名.可以从构造函数中，也可以从自定义DBContext中的存储字段中获取，您可以从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());
    }
}

更新: 我发现，如果在上面的构造函数中添加拦截器，则会导致内存泄漏. DotMemory不会告诉您这件事.确保将拦截器添加到静态构造函数中.

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

这篇关于在运行时更改表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！