查询使用实体框架，从动态创建的表中的数据 [英] Querying data using Entity Framework from dynamically created table

问题描述

TLDR;如何阅读使用实体框架表，在表名是不是在编译时已知数据？

有是处理堆积的信息，然后为每个批处理运行创建一个新的表，该表中存储一些数据的外部系统。这些新表的列布局前手是已知的，所以我就产生一个ADO.NET实体数据模型的（EDMX文件）的从现有的数据库，那里是完全相同的列的表布局。

在该数据库中的原始表被称为 ResultTableTemplate ，所以实体类再presenting该表也被称为 ResultTableTemplate 。

我试图弄清楚如何使用我的ADO.NET实体数据模型从这些动态创建的表来读取，并取回的IEnumerable＆LT; ResultTableTemplate＆GT; 。什么迄今为止我所做的是这样的：

 公开的IEnumerable＆LT; ResultTableTemplate＆GT; GetResultsFromTable（字符串tableName值）{
    使用（VAR上下文=新WorkdataEntities（））{
        变种表= context.CreateQuery＆所述; ResultTableTemplate＆GT;（选择+
            ALL_THOSE_COLUMN_NAMES ......+
            FROM+ tableName值;        VAR查询从表中选择逐项=;        返回query.ToList（）;
    }
}
 

当我运行查询，我得到一个 System.Data.EntitySqlException 以下消息：

  

ResultTable419828'不能在当前的范围来解决或
  上下文。确保所有被引用的变量范围，即
  所需的模式被加载，并且命名空间被引用
  正确。近成员访问前pression，1号线，列225。

ResultTable419828 是 tableName值

的价值

我曾尝试 tableName值+AS ResultTableTemplate，但它并没有帮助。

有没有办法前进了我，否则我将不得不这样做没有实体框架？

的帮助

编辑：我现在意识到，我写的查询文本不向下传递到底层的SQL Server实例的所有方法，但得到由实体框架返回一个PTED间$ P $ 的ObjectQuery＆LT; ResultTableTemplate＆GT; 实例，因此它看起来 ResultTable419828 自动生成的 DbSet 上下文的实例。

不过，是有我的方式做实现我需要做什么？

编辑： 拉吉斯拉夫Mrnka 。现在，我这样做：

 公开的IEnumerable＆LT; ResultTableTemplate＆GT; GetResultsFromTable（字符串tableName值）{
    使用（VAR上下文=新WorkdataEntities（））{
        变种查询= context.ExecuteStoreQuery＆所述; ResultTableTemplate＆GT;（选择+
            ALL_THOSE_COLUMN_NAMES ......+
            FROM+ tableName值;        返回query.ToList（）;
    }
}
 




解决方案

这是不能直接成为可能。当你映射实体 ResultTableTemplate 你辛苦code表此实体的名称。实体只能一次（每个型号），所以在运行时每个EF查询本实体的结果总是在查询 ResultTableTemplate 表映射。

要改变的是行为改变在运行时映射文件（SSDL）这是相当丑陋的黑客攻击，因为它需要你改变XML文件，并重新加载它的唯一方法。你将不得不在每次更改文件时间 MetadataWorkspace 手工打造。建筑 MetadataWorkspace 是EF性能最耗时的操作之一。在正常运行 MetadataWorkspace 创建每个应用程序只运行一次。

有简单的解决方法。你知道表名，你知道的表结构 - 它是固定的。因此，使用直接SQL和使用EF的结果物化到您的映射实体类：

  VAR表= context.ExecuteStoreQuery＆LT; ResultTableTemplate＆GT;（SELECT ... FROM+ tableName值）;
 

缺点是，你不能在这种方法中使用Linq，但你的要求不是非常适合EF

TLDR; How do I read data from a table using Entity Framework, when the table name isn't known at compile-time?

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.

The original table in that database is called ResultTableTemplate, so the entity class representing that table is also called 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();
    }
}

When I run the query, I get a System.Data.EntitySqlException with the following message:

'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 is the value of tableName

I have tried tableName + " AS ResultTableTemplate" but it did not help.

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 ResultTable419828 among the auto-generated DbSet instances of the Context.

Still, is there a way for me do achieve what I need to do?

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

解决方案

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.

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.

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

The disadvantage is that you cannot use Linq in this approach but your requirement is not very well suited for EF.

这篇关于查询使用实体框架，从动态创建的表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！