Querying data using Entity Framework from dynamically created table

Question

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

Answer 1

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.

Answer 2

Try this; :)

string tableName = "MyTableTest";

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