Implementing if-not-exists-insert using Entity Framework without race conditions

Question

Using LINQ-to-Entities 4.0, is there a correct pattern or construct for safely implementing "if not exists then insert"?

For example, I currently have a table that tracks "user favorites" - users can add or remove articles from their list of favorites.

The underlying table is not a true many-to-many relationship, but instead tracks some additional information such as the date the favorite was added.

CREATE TABLE UserFavorite
(
    FavoriteId int not null identity(1,1) primary key,
    UserId int not null,
    ArticleId int not null
);

CREATE UNIQUE INDEX IX_UserFavorite_1 ON UserFavorite (UserId, ArticleId);

Inserting two favorites with the same User/Article pair results in a duplicate key error, as desired.

I've currently implemented the "if not exists then insert" logic in the data layer using C#:

if (!entities.FavoriteArticles.Any(
        f => f.UserId == userId && 
        f.ArticleId == articleId))
{
    FavoriteArticle favorite = new FavoriteArticle();
    favorite.UserId = userId;
    favorite.ArticleId = articleId;
    favorite.DateAdded = DateTime.Now;

    Entities.AddToFavoriteArticles(favorite);
    Entities.SaveChanges();
}

The problem with this implementation is that it's susceptible to race conditions. For example, if a user double-clicks the "add to favorites" link two requests could be sent to the server. The first request succeeds, while the second request (the one the user sees) fails with an UpdateException wrapping a SqlException for the duplicate key error.

With T-SQL stored procedures I can use transactions with lock hints to ensure a race condition never occurs. Is there a clean method for avoiding the race condition in Entity Framework without resorting to stored procedures or blindly swallowing exceptions?

have you had a look at `using (var tran = new TransactionScope())`? — RPM1984, Nov 16 '10 at 08:55
^ Wrapping the code in **TransactionScope** doesn't work. @ShadowChaser were you ever able to find a solution to this? — Yusha, Apr 10 '19 at 16:42

abend · Answer 1 · 2011-04-22T20:05:04.800

1

You can also write a stored procedure that uses some new tricks from sql 2005+

Use your combined unique ID (userID + articleID) in an update statement, then use the @@RowCount function to see if the row count > 0 if it's 1 (or more), the update has found a row matching your userID and ArticleID, if it's 0, then you're all clear to insert.

e.g.

Update tablex set userID = @UserID, ArticleID = @ArticleID (you could have more properties here, as long as the where holds a combined unique ID) where userID = @UserID and ArticleID = @ArticleID

if (@@RowCount = 0) Begin Insert Into tablex ... End

Best of all, it's all done in one call, so you don't have to first compare the data and then determine if you should insert. And of course it will stop any dulplicate inserts and won't throw any errors (gracefully?)

edited Apr 22 '11 at 20:05

answered Apr 22 '11 at 19:47

abend

463
4
17

This can still cause race conditions. Say you try updating and nothing updates. By the time you check `@@RowCount` and actually perform the insert, another row could have been inserted. If you have unique constraints, this would throw an error, otherwise you have duplicated data. – Nelson Rothermel Mar 06 '12 at 16:51
1

@@RowCount will get only the affected rows from your last statement. it is valid for the last statement in your current context, and other user sessions have no impact on that. – abend Mar 13 '12 at 01:26

Henk van Dijken · Accepted Answer · 2011-04-18T20:13:50.267

-3

You could try to wrap it in a transaction combined with the 'famous' try/catch pattern:

using (var scope = new TransactionScope())
try
{
//...do your thing...
scope.Complete();
}
catch (UpdateException ex)
{
// here the second request ends up...
}

edited Apr 18 '11 at 20:13

answered Apr 18 '11 at 20:05

Henk van Dijken

249
1
6

3

I don't see what using an explicit transaction gets you here. As far as I can work out, the behavior will be exactly the same with or without the transaction. Could you explain it to me? – Mike Chamberlain May 30 '11 at 02:12
It's obviously less efficient then using a merge in a stored procedure, but this does appear to be the best option within EF, provided the transaction type is set correctly. We just recently using a similar solution for a more complex project than my original question - a full save command that had to handle merge conflicts. – ShadowChaser Jan 09 '12 at 06:09
6

I don't see what the enclosing TransactionScope adds either. If there is a race condition, you will get an UpdateException regardless, won't you? – aknuds1 May 04 '12 at 13:26

Implementing if-not-exists-insert using Entity Framework without race conditions

2 Answers2

Linked