I have many tables with the same model structure but with other table names with other data (in this case will be ~100 tables). I want to dynamically switch table name in runtime using Entity Framework (for example get name table from routing). Tables in database are dynamically adding and deleting by other script. There is a way to do this with good performance like this?
db.Table("TableName")<SpecificModel>.Where(x => x.ID == ID)
Do you want to do like this?
foreach (string tableName in new[] { "Table1", "Table2" })
{
var result = dbContext.Database.SqlQuery<SpecificModel>(string.Format("SELECT * FROM {0} WHERE ID=@p0", tableName), 1).FirstOrDefault();
}
I did something like this. http://nodogmablog.bryanhogan.net/2013/08/entity-framework-in-an-dynamics-nav-navision-envirnoment/
I had tables which were identical, but they had different names. For example, a customer table but with different prefixes for different companies.
[ACME$Customer]
[SuperCorp$Customer]
I ended up using dynamic compilation. It's a pretty detailed blog post so I won't go into it here.
You can do this:
// Gets entity type from specified namespace/assembly
Type entityType = Type.GetType(string.Format("Your.NameSpace.{0},{1}", entityName, "Assembly.Name"));
// Finds item to update based on its primary key value
var entity = _dbContext.Find(entityType, entityKey);
// Finds column to update preference for
PropertyInfo propertyInfo = entity.GetType().GetProperty(entityField);
// Set and update (date example given)
propertyInfo.SetValue(entity, isOptIn ? (DateTime?)null : (DateTimeOffset?)DateTime.Now, null);
_dbContext.SaveChanges();