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.

Answer 2

You can actually catch the UpdateException and handle the response.

Firstly, The following code will show the errors we are interested in from SQL:

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

This shows the following output:

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 can see that we need to catch SqlException values 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's a dupe... do something about it, 
        //depending on business rules, maybe discard new insert and attach to existing item
    }
    else {
        // it's some other error, throw back exception
        throw;
    }
}

Answer 3

I would harness the concurrency handling built into Entity Framework rather than write my own logic.

You add a concurrency token to the database. Typically this would be a RowVersion field:

Using FluentAPI:

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

Using Data Annotations:

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

Typically you then handle the DbUpdateConcurrencyException thrown when there is a problem:

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

References:

Configuring a concurrency token

Handling concurrency in Entity Framework

Optimistic concurrency patterns using Entity Framework

EDIT Just realised that I have misread your question. You aren't really talking about concurrency here, as your question title suggests. You just want to ensure that a record's natural key is unique. The way to do that is over here: https://stackoverflow.com/a/18736484/150342