What is best practice for checking for an existing record with a primary key while using Entity Fram-CodePudding

What is the best practice to handle the following situation?

It is known that many records (thousands) will be inserted with a fair possibility of a primary key exception. In some cases the exception should trigger some alternative queries and logic. In other cases it doesn't matter much and I merely want to log the event.

Should the insert be attempted and the exception caught?

Should a query be made to check for the existing record, then attempt the insert if none exists?

or Both?

I have noticed slightly better performance when merely catching the exception, but there's not a significant difference.

CodePudding user response：

There are couple of things over here.

One thing required is that you must have Primary Key Constraint on Column at DB Level.
Now at Entity framework level it is good if you check that record exists or not. So basically what happen you query for record using Primary Key and if it is found then it return the entity and then you make changes to entity and at last savechanges will save that entity.
Now if you are not able to find entity then you have to add entity.
If you try without query then it is problematic for EF and specially if multiple request try to update same record.
Now one more case is that, lets assume that there is possibility that multiple request can insert same record and so primary key constraint will help here and it will not allow duplication if you are generating primary key manually.
For update too, there is possibility of data loss if you are not taking care of concurrency.

CodePudding user response：

IMO It depends. If the client is responsible for generating a PK, using a UUID or Snowflake etc. where keys are expected to be unique then the first option is fine. Whether you bother with a retry after generating a new ID or simply fail the operation and ask the user to try again (as it should be a 1 in a billion exception, not the norm) is up to you. If the data is relying on sequences or user-entered meaningful keys it should be managed at the DB side using DatabaseGenerated.Identity and meaningless keys with related object graphs created and committed within a single SaveChanges call.

The typical concern around ID generation and EF is usually where developers don't rely on EF/the DB to manage the PK and FKs through navigation properties. They feel they need to know the PK in order to set FKs for related data, either saving the primary entity to get the PK or generating keys client-side. One of the key benefits of using an ORM like EF is giving it the related objects and letting it manage the inserting of PKs and FKs automatically.