使用实体框架从动态创建的表查询数据 [英] Querying data using Entity Framework from dynamically created table
问题描述
<强> TLDR;当编译时不知道表名时，如何使用实体框架从表中读取数据？
有一个外部系统处理大量信息，然后为每个批运行创建一个新表，并在该表中存储一些数据。这些新表的列布局是已知的，因此我已经从现有的数据库生成了一个ADO.NET实体数据模型（edmx文件），其中有一个表具有完全相同的列布局。
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.
该数据库中的原始表称为 ResultTableTemplate ，因此表示该表的实体类也称为 ResultTableTemplate 。
The original table in that database is called ResultTableTemplate, so the entity class representing that table is also called ResultTableTemplate.
我试图找出如何使用我的ADO.NET实体数据模型从这些动态创建表，并返回 IEnumerable< 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:
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();
}
}
当我运行查询时，我得到一个 System.Data .EntitySqlException 带有以下消息：
When I run the query, I get a System.Data.EntitySqlException with the following message:
'ResultTable419828'无法在当前范围内解决或
上下文确保所有引用的变量都在范围内，
所需的模式被加载，并且该命名空间被正确地引用
。成员访问表达式，第1行，第225列。
'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 的值为 tableName
我尝试过 tableNam e +AS ResultTableTemplate但没有帮助。
有没有办法为我，或者我不得不这样做没有实体框架的帮助？
Is there a way forward for me, or will I have to do this without the help of Entity Framework?
编辑：我现在意识到，我正在撰写的查询文本不会一直传递到底层SQL Server实例，但由Entity Framework解释，该框架返回一个 ObjectQuery< ResultTableTemplate> 实例，因此它查找 ResultTable419828 在上下文的自动生成的 DbSet 实例中。
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 ResultTable419828 among the auto-generated DbSet instances of the Context.
仍然有一种方法让我做到我需要做吗？
Still, is there a way for me do achieve what I need to do?
编辑：感谢 Ladislav Mrnka 。现在，我这样做：
Thanks Ladislav Mrnka. Now, I do this:
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();
}
}
推荐答案
这是不可能的，当您将实体映射到 ResultTableTemplate 硬编码这个实体的表名，实体只能被映射一次（每个模型），所以在运行时，这个实体的每个EF查询总是导致查询到 ResultTableTemplate 表。
It is not directly possible. When you map entity to ResultTableTemplate you 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 ResultTableTemplate table.
唯一的改变方法是在运行时修改映射文件（SSDL），这是非常糟糕的黑客攻击，因为它需要你更改XML文件并重新加载它必须在每次更改文件时手动创建 MetadataWorkspace 。构建 MetadataWorkspa ce 是EF中性能最佳的操作之一。在正常运行中 MetadataWorkspace 仅在每个应用程序运行时创建一次。
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 MetadataWorkspace manually every time you change the file. Building MetadataWorkspace is one of the most performance consuming operation in EF. In the normal run MetadataWorkspace is created only once per application run.
有简单的解决方法。你知道表名，你知道表结构 - 它是固定的。所以使用直接SQL并使用EF将结果实现到映射的实体类中：
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:
var table = context.ExecuteStoreQuery<ResultTableTemplate>("SELECT ... FROM " + tableName);
缺点是您不能在此方法中使用Linq，但您的要求不太适合EF 。
The disadvantage is that you cannot use Linq in this approach but your requirement is not very well suited for EF.
这篇关于使用实体框架从动态创建的表查询数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
