Querying data from a dynamically built table using Entity Framework

c# entity-framework sql

Question

The short version is: How can I get data from a table using Entity Framework if I don't know the table name at build time?

For each batch run, a new table is created and some data is stored in it by an external system that processes a large amount of data. Since the column structure of these new tables is already known, I created an ADO.NET Entity Data Model ((file edmx)) from a table in an existing database that has the exact same column layout.

The database's original table had the nameResultTableTemplate hence, the entity class corresponding to that table is also known asResultTableTemplate .

I'm attempting to find out how to read from those dynamically generated tables using my ADO.NET Entity Data Model and return data.IEnumerable<ResultTableTemplate> . I've done the following so far:

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

I get a when I perform the query.System.Data.EntitySqlException accompanied 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 what the valuetableName

I've attemptedtableName + " AS ResultTableTemplate" however it was ineffective.

Is there a solution for me, or will I have to do this task without Entity Framework's assistance?

EDIT: I now understand that Entity Framework interprets the query language I'm creating before passing it on to the underlying SQL Server instance, returning anObjectQuery<ResultTableTemplate> for instance, thus it searchesResultTable419828 Among the automatically producedDbSet examples of the Context

Is there a method for me to do what I need to do, though?

EDIT: Thank you, zz-87 zz. What I do next is

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();
    }
}
1
8
5/23/2017 12:00:59 PM

Accepted Answer

Directly, it is not feasible. When mapping an entity toResultTableTemplate The table's name is hardcoded into this object. Every EF query for this entity at runtime always results in a query to the model since entities can only be mapped once (per model).ResultTableTemplate table.

The only option to alter its behavior is to update the mapping file (SSDL) during runtime, which is an unsightly workaround that necessitates changing the XML file and reloading the application. You will need to constructMetadataWorkspace manually each time you make a file update. BuildingMetadataWorkspace is one of the operations in EF that uses the greatest processing power. normally speakingMetadataWorkspace is only ever produced once for each program execution.

There is a straightforward fix. You are familiar with the table's name and fixed structure. Therefore, materialize the result into your mapped entity class using straight SQL and EF:

var table = context.ExecuteStoreQuery<ResultTableTemplate>("SELECT ... FROM " + tableName);

The drawback of this technique is that Linq cannot be used, but your requirements are not well suited to EF.

10
1/17/2012 3:22:17 PM

Popular Answer

Attempt this:

string tableName = "MyTableTest";

// Fetch the table records dynamically
var tableData = ctx.GetType()
                .GetProperty(tableName)
                .GetValue(ctx, null);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow