I have a project where I need to design a code first entity system with an existing database. These existing tables in the database are not just any tables, they were being created by Microsoft's ERP tool "Dynamics Navision". The Navision tool holds different company information and creates different tables per company. Let's assume there are 3 types of tables;
- TableX
- TableY
- TableZ
And also there are 3 different companies;
- CompanyA
- CompanyB
- CompanyC
The tool created 9 different tables from above combinations. It simply duplicates all of the tables and adds a prefix with the company name to the table names. At the end, our database looks like this;
- CompanyA$TableX
- CompanyA$TableY
- CompanyA$TableZ
- CompanyB$TableX
- CompanyB$TableY
- CompanyB$TableZ
- CompanyC$TableX
- CompanyC$TableY
- CompanyC$TableZ
What Did I do so far?
So as you can see, there is an opportunity to simplify this architecture at the Entity Framework side. To achieve this, I created 3 entity classes for TableA, TableB, TableC and at the run time, I let the user to choose a company and according to the chosen company, I reflected my entity class with a custom TableAttribute where the table name prefixed with the company name. I am not going to give details on how I achieved it right now(but you can find implementation details on this article: LINK) but so far so good and I have applied all of the previous steps successfully and I managed to be dynamic on table names with the chosen company. However, there is another problem.
What is my question?
The problem is, even though I managed to create a system where I can change the entity class' table name attribute at the runtime and access to the target companies tables, there were no guarantee that duplicated tables with different company name prefixes are sharing %100 same inner field architecture. For example, CompanyA$TableX can have 5 fields while CompanyB$TableX has 6 fields where the first 5 fields are the same with CompanyA$TableX's fields but the last 1 field is extra. Let's also visualize it;
CompanyA$TableX's table fields;
- ID
- Name
- Surname
- Adress
- PhoneNumber
CompanyB$TableX's table fields;
- ID
- Name
- Surname
- Adress
- PhoneNumber
As you see, the Email field is the extra however the table names are the same(TableX), only the company differs and in my system, they share the same entity class and the company name determined at the run time as I mentioned before.
So, I want to know about how can I achieve to be dynamic on this. How I can have only one entity class but be dynamic in the fields of this class according to the chosen company. I want to know if it is possible technically and if it is, how to implement it. Thanks in advance.
