0

I have an API Endpoint that gets a lot of competitive requests. The code below periodically gives an error:

Violation of PRIMARY KEY constraint 'PK_Visitors'. Cannot insert duplicate key in object 'dbo.Visitors'.

var entityDB = await db.Visitors.FindAsync(entity.Id);

if (entityDB is not null)
{
    entityDB.Url = entity.Url;
    entityDB.Source = entity.Source;

    // etc...
}
else
{
    db.Visitors.Add(entity);
}

await db.SaveChangesAsync();

I understand very well that the problem occurs when the first request has started adding a record, but the second request in the FindAsync line does not yet see the new record and goes the same way adding a new one, which causes a primary key conflict.

Are there ways to avoid this problem with EF Core 7? I tried using transactions, but that didn't solve the problem.

  • The primary key is not generated on the SQL side, but in a separate external function. Therefore, for duplicate keys, it is assumed that the record will be updated.
sDima
  • 356
  • 2
  • 16

1 Answers1

0

The primary key is not generated on the SQL side, but in a separate external function.

If you are generating keys "client side" then you need to create a robust algorithm that will prevent duplicates from appearing. There is no other way.

One of common approaches in such cases - using Guid's.

Another approach is to generate a range of keys and store them on client managing the concurrent access on the client side.

Guru Stron
  • 102,774
  • 10
  • 95
  • 132
  • 1
    The question is not about duplicates. It's about the problem of concurrency. If the key is duplicated, then the system just needs to update certain fields of the record. This is necessary to solve the business tasks at hand. – sDima Mar 17 '23 at 22:05
  • @sDima then it is not possible with pure EF Core. It generates the query based on the state of change tracker, so the only thing is to retry in case of the failure. – Guru Stron Mar 17 '23 at 22:08
  • 1
    @sDima you can try looking into implementing upsert via stored procedure ([for example](https://stackoverflow.com/a/193876/2501279)). – Guru Stron Mar 17 '23 at 22:10
  • Is it possible to achieve the same behavior through EFCore transactions? – sDima Mar 19 '23 at 10:50
  • 1
    @sDima No. Just catch the PK violation exception and retry. – Guru Stron Mar 19 '23 at 11:32