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?

Answer 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?)