Avoiding concurrency issues with DbContext and Entity Framework

Question

I am building an ASP Web API project, using SQL Server for storage, and Entity Framework to connect to it.

Several tables in the storage contain unique constraints to enforce uniqueness of values in them. Before adding values to these tables, my application checks to ensure that the values do not exist.

The application is built so that one DbContext exists per request. It has occurred to me, however, that two requests could attempt to insert the same value into the table. Request A would check for the existence of the value in the table, as does Request B. Both ascertain that the value requires adding, and add it to the DbSet. When SaveChanges is called, one request will succeed, and one will fail due to the unique constraint failing it.

How can these sort of issues be resolved with Entity Framework? I am planning to create stored procedures to retrieve or create the values, which I think should solve it (the stored procedures guaranteeing the atomicity), but I would be keener on a way to solve this solely with Entity Framework itself. I think I like the idea of queueing up a load of actions that only get executed when DbContext.SaveChanges() is called, but obviously the stored procedure solution would mean that database interactions would take place before SaveChanges().

• smudge: Shouldn't the UNIQUE constraints take care of it for you? Even if both Request A and Request B determine that the value requires adding, whichever one executes second will return the SqlException (complaining about it violating the constraint). It's a rare occurrence, but you can still make sure you catch this and handle it accordingly.
• dark_perfect: Yes, exactly, the unique constraints will cause whichever request executes second to throw a SqlException, but I'm looking for a way to prevent any SqlExceptions from being thrown at all. I can catch the exception, but the best I can do is reattempt it, which requires a lot of extra work. I can fail the request too, but for obvious reasons, I'd rather not!
• smudge: I'm not sure I understand your question, then. If you build the application logic around catching the SQL Exception, you'll have a much easier time (it saves you the work of having to manually check for uniqueness). The way you're doing it now, there's always the chance for the concurrency issue you described, but if instead you rely on the database to tell you when you're inserting a duplicate all you have to do is make sure the exception gets handled properly.
• dark_perfect: I've always viewed checking for problems beforehand as much safer than letting something throw an exception and then handling it, not least because of the performance implications. Additionally, several entities might potentially be added to the database when SaveChanges is called (in the question, I phrased this as "queuing up a load of actions that only get executed when DbContext.SaveChanges() is called"). Whilst catching the SqlException would tell me that something went wrong, how would I determine that a unique constraint caused the exception, and how would I tell which entity caused it?
• smudge: Ah, got it. Yes, all good points. However, again, the nature of the problem is that the uniqueness constraint is ultimately enforced only by the database during the insert transaction. There is no 100%-effective way to check in advance. (However, you can perhaps improve the user experience by checking in advance, assuming there is no easy way to handle the exception without bubbling the error state to the UI.)

Answer 1

This problem is not specific to Entity Framework (although EF certainly also has other limitations around UNIQUE constraints). This is common with any similar server-client model. Since the uniqueness will be enforced by the database server at the time of the request, there is no fool-proof way to know in advance if the constraint will be violated, until you attempt the insert.

That said, checking for uniqueness in advance should be a very cheap operation (on SQL Server, a UNIQUE constraint will also give you an index on that column), so depending on how often you expect the application to be attempting duplicate insertion, this should cover the large majority of your cases. But if the duplicate insert attempts would be quite rare to begin with, you can avoid making the extra round-trip per insert and just handle the exception when it happens.

Handling the exception doesn't need to be very complicated, especially if there is a way for the end user to modify the data and attempt a retry. In the case where, as you said, you're queuing-up several inserts in a single transaction, you may need to check the exception (or inner exception) for specific constraint names, so that you know which entity object is a duplicate. (Again, EF doesn't really support handling UNIQUE constraint exceptions directly, so this is probably the part that is causing you the most pain right now.)

Note: I would not recommend re-working the insert as a stored procedure just to circumvent the rare chance that duplicate entries get attempted concurrently. You're better off making sure your application handles this (and other SQL exceptions, of which there may be many) as gracefully as possible. However, if you are finding that in your case there is really no other way, a sproc may be the easiest approach for now. Just keep in mind that it's probably foregoing more maintainable code in order to solve a very rare edge case.