CODEWITHSUNDEEP
c#sql-serverasp.net-mvcentity-framework
Obin
Obin

Reputation: 483

Dynamic table name with entity framework

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)

Upvotes: 18

Views: 24605

Answers (4)

Ahmed Salem
Ahmed Salem

Reputation: 67

if you decide to select data from dynamic table name so you will not able to set the type of the query

so here we try some reversed code to find the table name type from Model Namespace

then set context with this table and you can use where condition also if you want

var tableName = item.TableName;
    Type entityType = Type.GetType(string.Format("AlbashaSweets.Models.{0}", tableName));
    var data = _DBContext.Set(entityType);
    
    //AlbashaSweets.Models is Your Namespace

Upvotes: 0

nycdanielp
nycdanielp

Reputation: 185

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

Upvotes: 2

Bryan
Bryan

Reputation: 5481

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.

Upvotes: 5

Palanikumar
Palanikumar

Reputation: 7150

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

Upvotes: 7

Related Questions