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#.

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

I want to do some thing like this.

1. list<string> TableNames = new list<string>();
2. 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.

Answer 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

Answer 2

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).