Dynamic table names in Entity Framework linq
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
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 }