实体框架 6 - 在插入和并发之前检查记录是否存在 [英] entity framework 6 - check if record exists before insert and concurrency

问题描述

我有以下场景:

使用 ef6 的业务逻辑函数检查记录是否已存在.如果记录不存在，则将其插入到数据库中.

A business logic function that uses ef6 checks if a record already exists. If the record does not exists, it is inserted on the database.

类似的东西

if (!product.Skus.Any(s => s.SkuCodeGroup == productInfo.SkuCodeGroup && s.SkuCode == productInfo.SkuCode))
    AddProductSku();

我有多个线程调用这个业务逻辑函数.会发生什么:

I have multiple threads calling this business logic function. What happens is:

如果使用相同的参数同时调用该函数，则两个实例都会检查记录是否存在 - 并且它不存在.所以两个实例都插入了相同的记录.

If the function is called simultaneous with the same parameters, both instances checks if the record exists - and it does not exists. So both instances inserts the same record.

在第一个实例上调用 context.SaveChanges() 时，一切正常.但是第二个 SaveChanges() 会抛出异常，因为记录已经存在(数据库上有唯一索引).

When context.SaveChanges() is called on the first instance, all goes ok. But the second SaveChanges() throws an exception because the record already exists (there is an unique index on the database).

如何实现这一点以避免异常?

How can I implement this to avoid the exception?

我通过使用锁 {} 解决了这个问题，但它造成了我不想要的瓶颈.

I solved it by using a lock {}, but it creates a bottleneck that i don't want.

谢谢.

推荐答案

您实际上可以捕获 UpdateException 并处理响应.

You can actually catch the UpdateException and handle the response.

首先，以下代码将显示我们感兴趣的 SQL 错误:

Firstly, The following code will show the errors we are interested in from SQL:

SELECT error, description
FROM master..sysmessages
WHERE msglangid == 1033 /* eng */
  AND description LIKE '%insert%duplicate%key%'
ORDER BY error

这显示了以下输出:

2601  Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.
2627  Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.

所以，从这里我们可以看出，我们需要捕获 SqlException 值 2601 和 2627.

So, from this we can see that we need to catch SqlException values 2601 and 2627.

try {
    using(var db = new DatabaseContext()){
        //save
        db.SaveChanges(); //Boom, error
    }
}
catch(UpdateException ex) {
    var sqlException = ex.InnerException as SqlException;
    if(sqlException != null && sqlException.Errors.OfType<SqlError>()
         .Any(se => se.Number == 2601 || se.Number == 2627))  /* PK or UKC violation */
    {
        // it's a dupe... do something about it, 
        //depending on business rules, maybe discard new insert and attach to existing item
    }
    else {
        // it's some other error, throw back exception
        throw;
    }
}

这篇关于实体框架 6 - 在插入和并发之前检查记录是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！