Dynamic table name with entity framework

Question

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)

I can't help but wonder why you don't just normalize your tables into one table? If they are all the same structure... — Nathan A
– Nathan A, Commented Jun 24, 2015 at 19:38
The simplest way is just normalize but i can't do it in this project. Tables are need to be separated — Obin
– Obin, Commented Jun 24, 2015 at 20:00
In order to do what you are wanting to do you would need to update your edmx file with the new metadata of the created tables. I don't know of a way to do this at run time, because updating the edmx file would require a rebuild because new code would be generated. I think that Entity Framework currently does not support what you are trying to do. — tdbeckett
– tdbeckett, Commented Jun 29, 2015 at 15:41
Can you give us an example of the tables being created? Is the data changing radically between each table? What is the significance of each table i.e. what does each table represent? — tdbeckett
– tdbeckett, Commented Jun 29, 2015 at 15:42
Tables structure are the same like model (earlier named "SpecificModel") for each table. They differ in the table name like "Company_A", "Company_B", "Company_C" and data in this tables but not structure. Of course I can paste here all model with sql script to generate tables (grabbed from EF Code First) but i think it's not necessary and can blur whats a point of my problem — Obin
– Obin, Commented Jun 29, 2015 at 16:46

Palanikumar · Accepted Answer · 2015-06-27 04:43:45Z

7

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

edited Jun 27, 2015 at 4:43

answered Jun 27, 2015 at 4:36

Palanikumar

7,2004 gold badges44 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Obin Over a year ago

It works nice but it is still query. There is a way to do this with LINQ?

Palanikumar Over a year ago

Yes, I think we can do that in LINQ using Reflection and Expression, but little complicate once I get let you know. In entity framework, finally the linq expression will convert into sql query, so no more difference with that. Using this method also EF mapping your data into class object(SecificModel) right :)

tdbeckett Over a year ago

This will allow for sql injection.

tdbeckett Over a year ago

This will not work. The because the tables are being created dynamically and there will not be a SpecificModel that is know to EF for the return type.

Palanikumar Over a year ago

@tdbeckett, If there is no way to achieve the requirement via Linq, then we have to use this method and we need to properly validate the input string to avoid sql injection.

|

Bryan · Accepted Answer · 2015-08-31 15:44:57Z

5

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.

answered Aug 31, 2015 at 15:44

Bryan

5,58110 gold badges56 silver badges69 bronze badges

2 Comments

Trevortni Over a year ago

Do you know how well this would handle tables which are not identical, besides having different names?

Tausif Over a year ago

wrong link I believe this is the correct link nodogmablog.bryanhogan.net/2013/08/…

nycdanielp · Accepted Answer · 2018-09-14 16:37:50Z

2

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

answered Sep 14, 2018 at 16:37

nycdanielp

1851 silver badge2 bronze badges

Comments

Ahmed Salem · Accepted Answer · 2022-07-21 06:05:56Z

0

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

edited Jul 21, 2022 at 6:05

answered Jul 20, 2022 at 19:19

Ahmed Salem

677 bronze badges

1 Comment

Saeed Zhiany Over a year ago

Please read How do I write a good answer?. While this code block may answer the OP's question, this answer would be much more useful if you explain how this code is different from the code in the question, what you've changed, why you've changed it and why that solves the problem without introducing others. - From Review

Collectives™ on Stack Overflow

Dynamic table name with entity framework

4 Answers 4

6 Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related