1

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
this.harry
  • 23
  • 7
  • 3
    You can have 1 large DbContext for migration, and the others for isolation, querying, etc. or you can use a micro orm like dapper. Usually, ERP projects have very complex and dynamic db structures where EF may not be the best fit. – Eldar Jul 20 '23 at 08:14
  • How many tables are you talking about? At some point you might want to consider going the microservices approach, breaking some of the relations, and actually making completely independent databases. That is a huge organizational change, but if you have many teams it will have some scaling advantages. – JonasH Jul 20 '23 at 08:28
  • What about using interfaces for each domain context? An interface would expose the DB sets that are applicable to that domain and the actual context would implement it. – Aluan Haddad Jul 20 '23 at 11:36
  • Thanks @Eldar - i also came across this thread [stackoverflow.com](https://stackoverflow.com/questions/11197754/entity-framework-one-database-multiple-dbcontexts-is-this-a-bad-idea) where i read a comment from **Dave T.** about an article from Julie Lermann [microsoft.com](https://learn.microsoft.com/en-us/archive/msdn-magazine/2013/january/data-points-shrink-ef-models-with-ddd-bounded-contexts) where she describes how to have a "big" context for managing and domain-contexts for isolation. This seems to be the solution for EFCore. WIll read her book also! – this.harry Jul 20 '23 at 12:06
  • @JonasH - we have something about 350 Tables in the same scheme. We are indeed going for microservices to prepare a organizational change (therefore smaller dbcontexts would be very nice), but we are 3 developers and we will relaunch the apps very modular, so we dont have that big one launch that will also go with higher risk, especially because we are only 3 devs. – this.harry Jul 20 '23 at 12:22
  • @AluanHaddad - Yes that would be a "workaround" for isolation. But in the end every service would need to deal with the whole DbContext. Which maybe would be okay somehow - especially because EFCore is caching the Context Scheme (or something similar) so the big cost comes only on first initialization, BUT i would rather like to just have it split up so the service only has what it needs - not all the tables. – this.harry Jul 20 '23 at 12:26
  • @this.harry I understand. It's a very reasonable thing to want to do. The interface suggestion is just for clarity and maintainability, not performance or independent versioning. Perhaps as a proof of viability, experiment with a bifurcated schema in the database itself. See if you can define meaningful subsets of relations that don't have references outside of their schema. – Aluan Haddad Jul 20 '23 at 12:31
  • @Eldar - I know dapper only a little. So far is my opinion that the pros of EF would be to have the dataaccess more simple and organized than it would be with dapper. Maybe i am wrong, but i think to organize the dataaccess with dapper in a good way is a bit more work than have it orginzed the "EF-Way"? I think i need to have to dive into dapper a bit.. – this.harry Jul 20 '23 at 12:35
  • Something seem very wrong to me if you have 350 tables with a development team of 3 people. I would expect such a large database to be "enterprise", with dedicated Database Admins, multiple teams etc. To me that would suggest that you either have a poor database design, or are *severely* understaffed. – JonasH Jul 20 '23 at 12:42
  • Also, you can use both EF and dapper together. I have seen many projects that leverage EF for deployments, migrations, and cud (EF is very flexible for auditing, logging etc.) operations and dapper for querying. This way you can have one large context for tracking schema changes (migrations), and multiple small contexts for manipulating data and dapper for querying (or any other micro orm). – Eldar Jul 20 '23 at 12:52
  • @JonasH - haha, you have two points here. The db-design is not the best (wich is growing now for more than three decades!) and also we are understaffed. Which hopefully will change soon, now that we are relaunching several legacy projects (like that ERP-System). The somewhat-dba is retiring next year so the step into code-first (ef managed db) seemed to be an good option for us. – this.harry Jul 20 '23 at 13:32

1 Answers1

1

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?

Code-first or DB-first

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.

Single vs multiple contexts

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.

ArwynFr
  • 1,383
  • 7
  • 13
  • Knowing that my question could not be answered in a simple answer because it depends on several things. I think your answer faces the important facts to make a good decision. For anyone who is interested in how this answers my question: 1. Our Database is used by several services and a few apps; 2. The Database is the source of truth; 3. Further there are several Stored Procedures that are managed separately. Having managed the db by any (single) application would be wrong. In general it might be troublesome. So i will stick with db-first. Thanks a lot! – this.harry Jul 24 '23 at 13:31