Using Entity Framework to implement if-not-exists-insert without race conditions

entity-framework insert linq-to-entities

Question

Is there a reliable pattern or construct for securely implementing "if not exists then insert" in LINQ-to-Entities 4.0?

Users may add or delete items from their list of favorites in a table that I presently use as an example to monitor "user favorites."

The underlying database records some extra data, such as the date the favorite was added, rather than being a pure many-to-many connection.

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);

A duplicate key issue happens when two favorites are inserted with the same User/Article pair, which is what is wanted.

Currently, I'm using C# to implement the "if not existing then insert" logic in the data layer:

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();
}

This implementation's vulnerability to race circumstances is an issue. For instance, if a user hits the "add to favorites" link twice, the server can get two requests. The first call is successful, but the second, which the user sees, fails due to a duplicate key problem with an UpdateException wrapped in a SqlException.

I can utilize transactions with lock hints with T-SQL stored procedures to make sure a race scenario never arises. Is there a clean way to get around Entity Framework's race condition that doesn't use stored methods or mindlessly swallowing exceptions?

1
15
11/16/2010 12:22:54 AM

Accepted Answer

You may attempt to encapsulate it in a transaction and use the well-known try/catch pattern:

using (var scope = new TransactionScope())
try
{
//...do your thing...
scope.Complete();
}
catch (UpdateException ex)
{
// here the second request ends up...
}
-3
4/18/2011 8:13:50 PM

Popular Answer

Additionally, you may create a stored procedure using some of the new features of SQL 2005+.

Use your userID and articleID together as one unique ID in an update statement, and then check the row count using the @@RowCount function to see if it is greater than 0. If it is greater than 0, the update has found a row that matches your userID and ArticleID; if it is less than 0, you are free to insert.

e.g.

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

If (@@RowCount = 0), start inserting data into the tablex; end

The best part is that everything is completed in an one call, saving you from having to compare the data first before deciding whether to insert. Naturally, it will also prevent any duplicate insertion and refrain from throwing any errors (gracefully?



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow