.net - entity framework 6 - check if record exists before insert and concurrency

Question

I have the following scenario:

A business logic function that uses ef6 checks if a record already exists. If the record does not exists, it is inserted on the database.

Something like this

if (!product.Skus.Any(s => s.SkuCodeGroup == productInfo.SkuCodeGroup && s.SkuCode == productInfo.SkuCode))
    AddProductSku();

I have multiple threads calling this business logic function. What happens is:

If the function is called simultaneous with the same parameters, both instances checks if the record exists - and it does not exists. So both instances inserts the same record.

When context.SaveChanges() is called on the first instance, all goes ok. But the second SaveChanges() throws an exception because the record already exists (there is an unique index on the database).

How can I implement this to avoid the exception?

I solved it by using a lock {}, but it creates a bottleneck that i don't want.

Thank you.

Answer 1

We have had to deal with this same issue. There really is no good workaround if you don't want to implement a lock in your code. You also have to be sure there isn't, or won't be in the future, multiple ways for new rows to get into the database.

What we do is evaluate the exception message and if it's a duplicate key error, we simply eat the exception. In fact, we don't even check first to see if the row exists. SQL Server will do this for us anyway. So it saves a seek each time we do an insert. This approach works for us and our application. It may or may not work in all cases, depending on what you want to do after the insert.