Reference Table through a variable in Entity Framework

Question

I have 5 tables in database Table1, Table2 and so on [All tables have same column name or Table Definition]. I am using Entity Framework in MVC application and C#.

First creating an object of db of Database.
Getting table data as db.Table1.ToList();.

I want to do some thing like this.

list<string> TableNames = new list<string>();
db.TableNames[1].ToList();

Now I know this won't work but is there any way I can get data without hard coding the table names as my Project will deal with 100s of tables with same column names but different data. This is a Project for a Hospital which will receive data from different locations. Lets say for location A I am expecting 100 cases a day and right now I have 10 locations. So if I combine all this data into one which means 1000 records each day in a single day therefore overtime searching through this table will become performance sensitive.

If you have same column name, you should not be having hundreds of table. One single table (with an extra column) should be good enough.I suggest you think that direction. — Shyju
– Shyju, Commented Aug 27, 2016 at 16:40
This sounds like a database design error. Fix it now before its too late — RiggsFolly
– RiggsFolly, Commented Aug 27, 2016 at 16:43
Buddy Each table contains more than thousand of record of a location and if I am talking about 100s of table and I put the data of those 100s of table into one by creating a new column than the performance of the application will be adversely affected. — Sudhanshu
– Sudhanshu, Commented Aug 27, 2016 at 16:44

Sudhanshu · Accepted Answer · 2016-08-28 07:27:24Z

1

I am writing this for those who might occur into this same dilemma..... I had reference a table through EF so the classes got generated into the Model. Lets say I have 3 tables of same schema tbl_Loc1, tbl_Loc2 and tblLoc3.

public void getDataFromTable(string TableName)

{ using(var ctx = new DBEntities()) { string query ="Select * from " +TableName; var data=ctx.tbl_Loc1.SqlQuery(query);
}

}

DBEntities is Database Connection String
In ctx.tbl_Loc1.SqlQuery(query);.............. tbl_loc1 has a class in model which will help in getting data in the same format[As all tables have the same table definition]
There is a model of tbl_Loc1 in EF whereas tbl_Loc2 and tbl_Loc3 are only in Database.
Return this data as IEnumerable list
http://www.entityframeworktutorial.net/Querying-with-EDM.aspx

edited Aug 28, 2016 at 7:27

answered Aug 28, 2016 at 7:10

Sudhanshu

1201 silver badge12 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:51:41Z

I echo other commenter's thoughts that you probably can handle this all in one table with a distinguishing column (and some proper indexes on the table). What you've mentioned so far only amounts to hundreds of thousands of records, something that should still perform very well.

However, in order to do what you want the way you state it, you can use reflection to examine the properties of your db object. Any property in there that is a hashset is a property that represents a table, so you can get a list of all the hashset properties, and their names (perhaps with a few tweaks regarding pluralization), which will give you your table names.

For a more sophisticated use of metadata within EF, take a look at How I can read EF DbContext metadata programmatically?.

Also, you may find that SMO is a helpful approach to this kind of thing (nothing preventing you from using it and EF).

Collectives™ on Stack Overflow

Reference Table through a variable in Entity Framework

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related