Dynamic table names in Entity Framework linq

Question

I'm using Entity Framework 6 with ASP.Net MVC 5. When using a database context object, is there a way to use a variable for the table name, without having to manually write the query?

For example:

var tableName = "NameOfTable";

result = context.tableName.Find(...);

I know that particular code won't work, because tableName is not defined in context, but is there a way to achieve the desired effect?

There are some similar questions on this site, but they never really solved the problem and they were for earlier versions of entity framework, so I'm hoping that there is an answer now.

Answer 1

Here's a simple solution using a switch to associate a particular Type to a table. You could also maintain use some sort of Dictionary<string, Type> object.

var tableName = "Table1";
// Get proper return type.
Type returnType;
switch(tableName) {
    case "Table1":
        returnType = typeof(Table1EntityType);
        break;
    case "Table2":
        returnType = typeof(Table2EntityType);
        break;
}
var query = context.Set(returnType);
// Filter against "query" variable below...
var result = query.Where(...);

-or-

var tableName = "Table1";
Dictionary<string, Type> tableTypeDict = new Dictionary<string, Type>()
{
    { "Table1", Table1Type },
    { "Table2", Table2Type }
}; 
var query = context.Set(tableTypeDict[tableName]);
// Filter against "query" variable below...
var result = query.Where(...);

EDIT: Modified for Entity Framework

EDIT2: Use typeof per @thepirat000 's suggestion

Answer 2

In addition to the helpful answers above, I also want to add this in case it helps someone else.

• If you are getting this error on the "Where" clause in Mark's answer:

  'DbSet does not contain a definition for 'Where' and no acceptable extension method 'Where' accepting an argument of the type 'DbSet' could be found.

  Installing the Nuget Package "System.Linq.Dynamic.Core" made the error disappear for us.

• If you need to access the LINQ methods and the column names from the table, you can code something like this:

  var tableName = "MyTableName"; 
  var tableClassNameSpace = "MyProject.Models.EntityModels";
  
  using (var dbContext = new MyEntities()) 
  {
       var tableClassName = $"{tableClassNameSpace}.{tableName}";
       var dynamicTableType = Type.GetType(tableClassName);      // Type
       var dynamicTable = dbContext.Set(dynamicTableType);      // DbSet
  
       var records = dynamicTable
             .AsQueryable()
             .ToDynamicList()
             .OrderBy(d => d.MyColumnName)
             .Select(d => new { d.MyColumnName })
             .ToList();
  
        // do stuff
  }