在运行时更改表名 [英] 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()

是否可以在运行时和按需将 db 表名重定向到 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屋！