Entity structure 6 - check if record exists before insert and concurrency

Question

I have the following script:

A business logic function using ef6 checks to see if a record exists. If the record does not exist, it is added to the database.

Something like that

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

I have several threads invoking this business logic function. What's happening:

If the function is called simultaneously with the same parameters, both instances check if the record exists - and it does not exist. Therefore, both instances insert the same record.

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

How can I implement this to avoid an exception?

I solved this by locking {}, but this creates a bottleneck that I don't want.

Thanks.

Answer 1

We had to deal with the same problem. There is actually no good workaround if you do not want to inject the lock into your code. You must also be sure that in the future there will not be or will not be in the future several ways to access new rows in the database.

What we do is evaluate the exception message, and if it is a duplicate of a key error, we simply eat the exception. In fact, we don’t even check if the string exists. SQL Server will do this for us anyway. Thus, it saves the search every time we do an insert. This approach works for us and for our application. It may or may not work in all cases, depending on what you want to do after insertion.

Answer 2

You can catch an UpdateException and handle the response.

First, the following code displays the errors that interest us in SQL:

 SELECT error, description FROM master..sysmessages WHERE msglangid == 1033 /* eng */ AND description LIKE '%insert%duplicate%key%' ORDER BY error 

The following output is displayed here:

 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls. 2627 Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls. 

So, from this we see that we need to catch the values ​​of SqlException 2601 and 2627 .

 try { using(var db = new DatabaseContext()){ //save db.SaveChanges(); //Boom, error } } catch(UpdateException ex) { var sqlException = ex.InnerException as SqlException; if(sqlException != null && sqlException.Errors.OfType<SqlError>() .Any(se => se.Number == 2601 || se.Number == 2627)) /* PK or UKC violation */ { // it a dupe... do something about it, //depending on business rules, maybe discard new insert and attach to existing item } else { // it some other error, throw back exception throw; } } 

Answer 3

I would use the concurrency processing built into the Entity Framework instead of writing my own logic.

A concurrency token is added to the database. This will usually be a RowVersion field:

Using FluentAPI:

 modelBuilder.Entity<OfficeAssignment>() .Property(t => t.Timestamp) .IsRowVersion(); 

Using data annotations:

  [Timestamp] public byte[] RowVersion { get; set; } 

You usually handle the generated DbUpdateConcurrencyException when a problem occurs:

  using (var context = new SchoolDBEntities()) { try { context.Entry(student1WithUser2).State = EntityState.Modified; context.SaveChanges(); } catch (DbUpdateConcurrencyException ex) { Console.WriteLine("Optimistic Concurrency exception occured"); } } 

Literature:

Concurrency Token Setting

Concurrency handling in Entity Framework

Optimistic concurrency templates using Entity Framework

EDIT Just realized that I misunderstood your question. You really don't talk about concurrency here, as your question says. You just want the unique natural key to be unique. The way to do it is here: fooobar.com/questions/1173288 / ...