In our company we are going to relaunch a proprietary ERP System and for the backend we will use EFCore for dataaccess.
As usual the database design is not the best, but will do the work. But for EFCore we are facing a problem we are unsure how to handle. We would like to have multiple DbContexts for each domain. But the database does have multiple relations across these boundaries and every table is in the same scheme.
When we would migrate to code-first, so we can manage the db from code, we could not manage all relations because in a DbContext not every relation is used.
-> Is it possible to manage the whole db with those kind of "incomplete" DbContext-Relations with code-first? Or would be the only way to have one extraordinary DbContext? Or is there another way?
So for now the alternative seems to be to go all in for reverse engineering the db into entities and always let overwrite the entities (with scaffolding) when db scheme changes occur.
I already tried to create a single DbContext. With that I can manage the whole Db but it is very unpleasant and the creation of the context lasts horribly long.
Also I tried creating multiple Contexts but then I can not manage relations that are not inside a DbContext.
At this time we are reverse engineering the DbContexts, which works very well. But we need to manage the Database outside the codebase which does add complexity to deployment and versioning.
Is it possible to manage the whole db with those kind of "incomplete" DbContext-Relations with code-first? Or would be the only way to have one extraordinary DbContext? Or is there another way?
The question around code-first vs db-first depends on which part of your system is considered the source of truth and which is an output artefact.
In a DB-first approach, you maintain a database schema and the code is considered as an output of the scaffolding process. This code is usually considered disposable as you will re-scaffold it each time the db schema is updated. This scenario is well fitted for accessing legacy databases, or interop situations where you need finer control of the db scheme because multiple applications access the same database.
In a code-first approach, your database schema is considered a disposable artefact. Its generated as an output of the application of db migrations orchestrated by EF core. This is well fitted for application that have an exclusive access to the database, as the database schema details are not relly important as it's only prupose is to perist data of a given object model.
To solve the problem of dbcontext too large, you can of course split your data access layer into multiple smaller contexts. In that context though, you are using EF core to access the database, not to persist a given object model. Doing code-first approach in that context is not coherent, and I would recommend you stick with db-first. This of course requires you to update the database scheme then rescaffold impacted contexts.
As you already noted, EF core can only handle relationships between tables mapped by entities within their context. However there is no restriction on which tables get mapped to which contexts. Some may be bound multiple times. So for instance, Context 1 can access tables A and B, and Context 2 can access tables B and C. This way, you can handle all relationships in your database. However, this approach is even more incompatible with code-first approach, since you would have multiple sources of truth for a given table. Sticking with a db-first approach is fine though.
On a longer term approach, you could try to split your database model into multiple separate schemas (if your RDBMS engine supports it) and try to work around the intrication of relationships accross them. Then move from the db-first approach to code-first. Beware such change is a very complex one at the business level and will introduce the kind of challenge you face when developping DDD / microservices applications.