使用实体框架在没有竞争条件的情况下实现if-not-exists-insert [英] Implementing if-not-exists-insert using Entity Framework without race conditions

问题描述

使用LINQ-to-Entities 4.0，是否存在用于安全地实现如果不存在则插入"的正确模式或构造?

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.

我目前已经使用C#在数据层中实现了如果不存在则插入"逻辑:

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

此实现的问题在于它容易受到竞争条件的影响.例如，如果用户双击添加到收藏夹"链接，则可以将两个请求发送到服务器.第一个请求成功，而第二个请求(用户看到的)失败，并且UpdateException包装了一个SqlException以解决重复的键错误.

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.

通过T-SQL存储过程，我可以使用带有锁定提示的事务来确保从未发生竞争条件.是否有一种干净的方法来避免Entity Framework中的争用情况，而无需诉诸存储过程或盲目吞下异常?

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?

推荐答案

您可以尝试将其包装在与著名的" try/catch模式结合在一起的事务中:

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...
}

这篇关于使用实体框架在没有竞争条件的情况下实现if-not-exists-insert的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！