0

I am working with EF6, MSSQL, Oracle, .NET4.5 on a system that is used globally across company (many departments) to query different databases that belong to our department, that have mostly same EF model, some databases are Oracle and some are Microsoft SQL, some are development or uat, some are logs.

I am using different EF models for Oracle and for MSSQL databases.

One requirement is to switch between databases at run time, and this is easy,

public AggregatorEntities(string connectionString) : base(connectionString) { }

however it does have side effects - many databases (dev, uat, dr, logs,...) are out of sync from what Live is (model is generated from Live), which results in errors when querying those databases.

Management knows about situation and they are ok for devs that work on some specific database to do changes to global querying system that would allow testers and uat to query the data. However they want changes they have to do to take minimum time to do this - as it is additional cost to each project that involves database changes. I will basically need to build a 'can handle all' resilient system, that when one changes database in EF will do something to accommodate to specific database.

There are different failure scenarios:
1. Name of column on table is the same but Type is different in entity
2. No column on table but there is one on entity in EF
3. Additional columns on table that are not on EF
4. Additional tables in database that are not in EF model
5. No table in database but there is entity in EF model.

I have done some thinking and this question is broad and might get closed for same reason. However I am not sure if it is worth splitting the question into each scenario, as it depends on the answer. The way I understand if single answer can answer all points then no need to split, however if each situation has different 'cure' then question should be split for that part only, but without answer no way to know.... (catch 22).

Only option I see ATM is to generate it's own model for each mirroring database, but then I end up with 50+ models.

How do I allow EF to work with different database structures at run-time?

Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
  • ORMs are the opposite of the "can-handle-all" approach. They are the hard-coded-schema-in-the-application approach. – Panagiotis Kanavos Jun 21 '16 at 09:03
  • @PanagiotisKanavos I know, system is already half built thought and this was missed, so now we need to 'handle it'. – Matas Vaitkevicius Jun 21 '16 at 09:04
  • In the database world, views and stored procedures are used to abstract and hide schema changes. You *can* bind to views with most ORMs – Panagiotis Kanavos Jun 21 '16 at 09:04
  • @PanagiotisKanavos Nice idea, but databases are 150+ tables so to hide them behind views would require substantial work, and I think business might deem it to be to much of the change, I will bring this up with them. – Matas Vaitkevicius Jun 21 '16 at 09:06
  • You can't handle *that* scenario with an ORM. It's the *exact* opposite of what an ORM can do. In any case, *data type* changes are a *big* change that affects your application code as well. Changing eg from `datetime` to `datetimeoffset` may be possible in the database, but that will break code that can't handle timezones. Same for changes eg from int to decima. You can't just cover it up. – Panagiotis Kanavos Jun 21 '16 at 09:07
  • Atm I am using aspect oriented approach since db is read only in regards to the system so in T4 based on column names I add interfaces and then query through them, `public interface IPolicyNumber : IPolicyNumber { T POLICY_NO { get; set; } } public interface IPolicyNumber { }` they look like this so allow to query different types at runtime – Matas Vaitkevicius Jun 21 '16 at 09:09
  • Consider this: if you have mixed up chages on databases called `dev` and `uat`, you don't have a UAT database anymore, you have an outdated development database. You *have* to clean up the databases and you *have* to take care of versioning and migrations. That's why EF migrations are important. – Panagiotis Kanavos Jun 21 '16 at 09:09
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115186/discussion-between-matas-vaitkevicius-and-panagiotis-kanavos). – Matas Vaitkevicius Jun 21 '16 at 09:10
  • This isn't aspect-oriented programming. Even if it were, AOP is irrelevant at this point. The real problem is how you handle schema migrations and drift. – Panagiotis Kanavos Jun 21 '16 at 09:10
  • @PanagiotisKanavos so any advice, that doesn't involve me going to management and telling them they can't have it? :) (I can have db model per database worst case so it's not checkmate yet) – Matas Vaitkevicius Jun 21 '16 at 09:21

1 Answers1

0

This now officially cannot be done in a proper manner.

However end result of being able to switch between different databases with similar structures still can be achieved (for those without morals). Part with removing columns can used.

Solution is to have all inclusive EF model that is generated from database that has all the tables and all the columns (that are in any database think like logical OR of everything). Then model with all entities that have all properties from all db environments can be removed specific to environment that is queried at runtime in mechanism described here. This does not cover cases where type of column changes.

Hope this saves you some time as it took 2 weeks from mine...

Community
  • 1
  • 1
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265