1

When making changes to DbSet<> (like Add/Remove) what would be the order in which 'insert'/'delete' operations will be performed during SaveChanges?

Currently I'm observing 'deletes' before 'inserts' and that's what I would expect but couldn't find any official docs about it.

The problem I'm trying to prevent is that entities in question are controlled using unique constraint. And so I need to 'delete' old and 'insert' new ones. Doesn't matter in which order the 'inserts' would happen. What matters is the order of 'deletes' before 'inserts'.

Is this order described and fixed or we are forced to make SaveChanges in-between?

UPDATE_01:

Some more details.

Here is a sample minimized table schema:

CREATE TABLE "ClientContracts" (
    "Id" int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    "ClientId" int4 NOT NULL,
    "Code" text NOT NULL,
    "IsEnabled" bool NOT NULL,
    CONSTRAINT "PK_ClientContracts" PRIMARY KEY ("Id")
);
CREATE UNIQUE INDEX "IX_ClientContracts_ClientId_IsEnabled" ON public."ClientContracts" USING btree ("ClientId", "IsEnabled") WHERE "IsEnabled";
CREATE UNIQUE INDEX "IX_ClientContracts_Code" ON public."ClientContracts" USING btree ("Code");

A client (ClientId) can have multiple contracts assigned to it. But only one of them is active in a given moment (IsEnabled) controlled with a IX_ClientContracts_ClientId_IsEnabled constraint. Also each contract's Code is unique and controlled with a IX_ClientContracts_Code constraint. There are cases when a contract could be deleted by a client and added again with the same Code. And sometimes it could an active one.

In order to fulfill both constraints I see 2 ways:

  1. first delete the needed contracts (the list of contracts is supplied by an external service), SaveChanges, insert new ones and SaveChanges again. This way we can be sure that constraints won't be 'triggered' since we have securely deleted previous entries before adding those that are in conflict.
  2. DbSet<ClientContracts>.Add/Remove then SaveChanges and make EF handle deletes before inserts.

Multiple users and simultaneous changes are not accounted here. Single user at a time. Also an external service tries it's best to make sure that multiple contracts with the same code are not sent for processing (constraints are the 'last line of defense here').

So the main question is either we take the route (1) or (2).

Of course the most safest is (1) since we control the flow in this case. But this results in more code and more batches/connections. So perhaps could it be safe to go for (2)?

And again. We are not talking about order of inserts or deletes themselves (i.e. doesn't matter if entity_1 is deleted after entity_3 or entity_5 is inserted before entity_1). Just about that deletes comes before inserts.

Kasbolat Kumakhov
  • 607
  • 1
  • 11
  • 30
  • Can adding `SaveChanges` after every chain of deletes and inserts be the cure? – Slepoyi Apr 10 '23 at 08:28
  • That is what the last sentence is about. Are we forced to split the chain of deletes and inserts or will EF Core do this for us? – Kasbolat Kumakhov Apr 10 '23 at 08:30
  • Maybe this can help: https://stackoverflow.com/questions/19289371/is-it-possible-to-explicitly-set-the-order-of-operations-for-savechanges – Slepoyi Apr 10 '23 at 08:39
  • @Slepoyi calling SaveChanges after every change is the critical, EF-destroying bug. The order doesn't matter. A DbContext already is a disconnected Unit-of-Work that tracks and persists all changes in the correct order when `SaveChanges` is called – Panagiotis Kanavos Apr 10 '23 at 08:51
  • @KasbolatKumakhov why would the order matter? Why would `controlled using unique constraint.` be a problem at all? All EF Core applications use PKs which are unique by definition, and have no problems. If you mean auto-generated ID values, neither `IDENTITY` nor `Autonumber` columns have any issues. – Panagiotis Kanavos Apr 10 '23 at 08:57
  • The only way an autogenerated ID would cause a problem is a buggy MAX+1. That's a bug of the algorithm itself though, not EF. You'd get duplicates even in a single-used desktop application - just delete the last row and after 1 minute insert a new one. You'd get a duplicate, no matter what ORM or query you used. – Panagiotis Kanavos Apr 10 '23 at 09:00
  • @PanagiotisKanavos, It's not PK or autogenerated property. It's a separate field in entity which marks it to be unique among others. Imaging something like multiple number of contracts per client and only one of them to be active at a given time marked with a boolean field. Currently I'm not able to change table schema and need to adapt to it. – Kasbolat Kumakhov Apr 10 '23 at 09:09
  • Explain the *actual* problem in the comments and add enough code to reproduce it. Whatever it is, it's been encountered and solved by other developers in the past, with or without an ORM. Even if you used raw SQL you'd still have the exact same problem. – Panagiotis Kanavos Apr 10 '23 at 09:39
  • `I'm not able to change table schema` what is that schema? I suspect you tried to implement a "history" table without a proper PK, but a combination of a repeating ID and what? A date field? An `Active` field would only allow 2 items at a time. EF can handle this as long as you use the *actual* primary key in the configuration. If that means you need a composite PK, you'll have to configure a composite PK – Panagiotis Kanavos Apr 10 '23 at 09:45
  • If you change the value of the PK *don't do that*. ORMs (and most data applications in general) **depend** on immutable primary keys. Besides, it's just good database design. What happens if *another* connection/client modifies the primary key? You'd end up modifying the wrong record or worse, mangle the changes made by another connection – Panagiotis Kanavos Apr 10 '23 at 09:48
  • @PanagiotisKanavos, I have added some more details and a sample table schema. – Kasbolat Kumakhov Apr 10 '23 at 11:42
  • Why not *update* the existing objects instead of deleting them? Do you get a constraint violation when you try to move `Active` to another or to a new `ClientContract`? – Panagiotis Kanavos Apr 10 '23 at 11:50
  • @PanagiotisKanavos, It won't change much. If using 'update' instead of 'delete' the issue will be the same - updates (instead of deletes) before inserts, since there has to be insert anyway (that's a requirement) we can't replace one with another. Of course we can first add a non-active contract and then update the previous one to disable it and update the new one to enable, but the case with deletions is still relevant. And using this method will make code more complicated and add another update operation. – Kasbolat Kumakhov Apr 10 '23 at 12:05

0 Answers0