C# 使用实体框架从动态创建的表中查询数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8896843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Querying data using Entity Framework from dynamically created table
提问by Anders Tornblad
TLDR; How do I read data from a table using Entity Framework, when the table name isn't known at compile-time?
TLDR；当编译时不知道表名时，如何使用 Entity Framework 从表中读取数据？
There is an external system that processes a bulk of information, and then creates a new table for each batch run, and stores some data in that table. The column layout of these new tables are known before-hand, so I have generated an ADO.NET Entity Data Model (edmx file)from an existing database, where there is a table with the exact same column layout.
有一个外部系统处理大量信息，然后为每个批处理运行创建一个新表，并将一些数据存储在该表中。这些新表的列布局是事先知道的，因此我从现有数据库生成了一个 ADO.NET 实体数据模型（edmx 文件），其中有一个具有完全相同列布局的表。
The original table in that database is called ResultTableTemplate, so the entity class representing that table is also called ResultTableTemplate.
调用该数据库中的原始表ResultTableTemplate，因此也调用表示该表的实体类ResultTableTemplate。
I am trying to figure out how to use my ADO.NET Entity Data Model to read from those dynamically created tables, and getting back IEnumerable<ResultTableTemplate>. What I have done so far is this:
我试图弄清楚如何使用我的 ADO.NET 实体数据模型从那些动态创建的表中读取，并返回IEnumerable<ResultTableTemplate>. 到目前为止我所做的是：
public IEnumerable<ResultTableTemplate> GetResultsFromTable(string tableName) {
using (var context = new WorkdataEntities()) {
var table = context.CreateQuery<ResultTableTemplate>("SELECT " +
"ALL_THOSE_COLUMN_NAMES... " +
"FROM " + tableName;
var query = from item in table select item;
return query.ToList();
}
}
When I run the query, I get a System.Data.EntitySqlExceptionwith the following message:
当我运行查询时，我收到System.Data.EntitySqlException以下消息：
'ResultTable419828' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near member access expression, line 1, column 225.
无法在当前范围或上下文中解析“ResultTable419828”。确保所有引用的变量都在范围内，加载了所需的架构，并且正确引用了命名空间。Near 成员访问表达式，第 1 行，第 225 列。
ResultTable419828is the value of tableName
ResultTable419828是 tableName
I have tried tableName + " AS ResultTableTemplate"but it did not help.
我试过了，tableName + " AS ResultTableTemplate"但没有帮助。
Is there a way forward for me, or will I have to do this without the help of Entity Framework?
对我来说有没有办法，或者我必须在没有实体框架的帮助下做到这一点？
EDIT:I realize now that the query text I am writing is not passed all the way down to the underlying SQL Server instance, but gets interpreted by Entity Framework which returns an ObjectQuery<ResultTableTemplate>instance, so it looks for ResultTable419828among the auto-generated DbSetinstances of the Context.
编辑：我现在意识到我正在编写的查询文本并没有一直向下传递到底层 SQL Server 实例，而是由返回ObjectQuery<ResultTableTemplate>实例的实体框架解释，因此它在上下文ResultTable419828的自动生成的DbSet实例中查找.
Still, is there a way for me do achieve what I need to do?
不过，有没有办法让我实现我需要做的事情？
EDIT:Thanks Ladislav Mrnka. Now, I do this:
编辑：感谢Ladislav Mrnka。现在，我这样做：
public IEnumerable<ResultTableTemplate> GetResultsFromTable(string tableName) {
using (var context = new WorkdataEntities()) {
var query = context.ExecuteStoreQuery<ResultTableTemplate>("SELECT " +
"ALL_THOSE_COLUMN_NAMES... " +
"FROM " + tableName;
return query.ToList();
}
}
采纳答案by Ladislav Mrnka
It is not directly possible. When you map entity to ResultTableTemplateyou hardcode the name of the table for this entity. Entities can be mapped only once (per model) so at runtime every EF query for this entity always results in query to ResultTableTemplatetable.
这不是直接可能的。当您将实体映射到ResultTableTemplate您时，对该实体的表名称进行硬编码。实体只能映射一次（每个模型），因此在运行时，对该实体的每个 EF 查询总是导致查询到ResultTableTemplate表。
The only way to change is behavior is modifying mapping file (SSDL) at runtime which is quite ugly hack because it requires you to change XML file and reload it. You will have to build MetadataWorkspacemanually every time you change the file. Building MetadataWorkspaceis one of the most performance consuming operation in EF. In the normal run MetadataWorkspaceis created only once per application run.
改变行为的唯一方法是在运行时修改映射文件 (SSDL)，这是非常丑陋的黑客，因为它需要您更改 XML 文件并重新加载它。MetadataWorkspace每次更改文件时都必须手动构建。构建MetadataWorkspace是 EF 中最消耗性能的操作之一。在正常运行MetadataWorkspace中，每次应用程序运行仅创建一次。
There is simple workaround. You know the table name and you know the table structure - it is fixed. So use direct SQL and use EF to materialize the result into your mapped entity class:
有一个简单的解决方法。您知道表名和表结构 - 它是固定的。因此，使用直接 SQL 并使用 EF 将结果具体化到您的映射实体类中：
var table = context.ExecuteStoreQuery<ResultTableTemplate>("SELECT ... FROM " + tableName);
The disadvantage is that you cannot use Linq in this approach but your requirement is not very well suited for EF.
缺点是您不能在这种方法中使用 Linq，但您的要求不太适合 EF。
回答by Muru Bakthavachalam
Try this; :)
尝试这个; :)
string tableName = "MyTableTest";
// Fetch the table records dynamically
var tableData = ctx.GetType()
.GetProperty(tableName)
.GetValue(ctx, null);
