实体框架-处理重复密钥的PK/UKC 2601违规 [英] Entity Framework - Handling PK/UKC 2601 violations for duplicate keys
问题描述
在应用程序中的某个时刻，需要进行大量的处理，并且在dbcontext中创建了具有多个不同实体的可观图形以进行插入.考虑以下实体，它是更大模型的一部分:
At some point in the application, there's some heavy processing and a considerable graph with multiple different entitites is created in the dbcontext for insertion. Consider the following entities, part of a bigger model:
public class Wall
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Post> Posts { get; set; }
public ICollection<User> Users { get; set; }
}
public class Post
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Labels> Labels { get; set; }
}
public class Label
{
public int Id { get; set; }
[Index("IX_UniqueNameKind", IsUnique = true, Order = 1)]
[MaxLength(255)]
public string Name { get; set; }
[Index("IX_UniqueNameKind", IsUnique = true, Order = 2)]
[MaxLength(60)]
public string Kind { get; set; }
public ICollection<Post> Posts { get; set; }
}
我在Post和Label之间建立了多对多关系，并带有关联表"PostLabel"，以避免多余的db条目并优化空间使用.每个标签的唯一性都由名称"和种类"定义.
I have a many-to-many relationship between Post and Label with an assocation table "PostLabel" to avoid redundant db entries and optimize space usage. Each label uniquess is defined by "name" and "kind".
当多个用户可能正在运行相同的进程并插入相同的标签(名称，种类)，从而导致EntityFramework的SaveChanges引发DbUpdateException异常时，会发生此问题.
The problem occurs when multiple users could be running the same process and inserting the same label (name, kind), causing EntityFramework's SaveChanges to throw an DbUpdateException exception.
当前，我正在分离未能插入的标签"，而是从数据库中关联现有的标签".
Currently, I'm detaching the "labels" which failed to be inserted and associating the existing "labels" from the db instead.
public override int SaveChanges()
{
while (!isSaved)
{
try
{
// save data
result = base.SaveChanges();
// set flag to exit loop
isSaved = true;
}
catch (DbUpdateException ex)
{
var sqlException = ex.InnerException?.InnerException as SqlException;
if (sqlException != null && sqlException.Errors.OfType<SqlError>().Any(se => se.Number == 2601 || se.Number == 2627) && ex.Entries.All(e => e.Entity.GetType() == typeof(Label))
{
// handle duplicates: find existing record in DB and associate it to the parent Post entity.
var entries = ex.Entries;
foreach (var entry in entries)
{
HandleLabelDuplicates(entry);
}
}
else
{
throw;
}
}
}
return result;
}
private void HandleSourceSegmentLabelDuplicates(DbEntityEntry entry)
{
var labelWhichFailedToInsert = (Label)entry.Entity;
var labelAlreadyInDatabase = Labels.Single(t => t.Name.Equals(labelWhichFailedToInsert.Name) && t.Kind.Equals(labelWhichFailedToInsert.Kind));
// fix label association in all "Posts" which contain this label.
foreach (var post in labelWhichFailedToInsert.Posts)
{
// fix the reference to the existing label in the database, instead of inserting a new one.
post.Labels.Add(labelAlreadyInDatabase);
}
// change state to remove it from context
entry.State = EntityState.Detached;
}
这里的问题是，整个DbContext会被插入多次，更精确地说，每次处理一个异常加1时，因此，如果找到一个重复项，则整个模型将在数据库中插入两次.
The problem here is that the whole DbContext is inserted multiple times, more precisely everytime an exception is handled plus 1, so if one duplicate is found, the whole model is inserted twice in the DB.
我的猜测是，在第一次尝试SaveChanges时，成功插入的所有实体都不会因为抛出异常而将其状态更新为未更改"，但是插入是在SQL事务中进行的，因此是第二次尝试保存到SaveChanges将再次插入它们.
My guess is that at the first attempt to SaveChanges, all the entities inserted successfully don't get their state updated to "Unchanged" since an exception is thrown, however the insertions are in the SQL transaction, consequently, the 2nd attempt to SaveChanges will insert them again.
有什么想法吗?
整个工作都在一个事务中完成:
The whole work is being done within a transaction:
using (var transaction = context.Database.BeginTransaction())
{
// some work
context.Orders.Add(order);
context.SaveChanges();
// some more work where some id's are needed
context.SaveChanges();
transaction.Commit();
return order.Id;
}
如果包装在事务中时处理异常/重复项，问题似乎是在重复SaveChanges()，如果我从事务中解开所有内容，它应该可以正常工作.
It appears that the problem is repeating SaveChanges() when handling exceptions/duplicates when wrapped within a transaction, if I unwrap everything from the transaction it works as it should.
推荐答案
我在这里看不到 lock()，这可能是您可以考虑的解决方案之一.锁定操作，等待更新完成，然后再次恢复.
I see no lock() here, this could be one of the solutions you can consider.
Lock the operation, wait for the update to complete and resume again.
第二，我看不到 DbUpdateConcurrencyException 处理，因此您可以考虑的另一种解决方案是:
Second, I see no DbUpdateConcurrencyException handling, so another solution you may consider is:
using (var context = new Ctx())
{
//your logic
while (!saved)
{
try
{
// Attempt to save changes to the database
context.SaveChanges();
saved = true;
}
catch (DbUpdateConcurrencyException ex)
{
foreach (var entry in ex.Entries)
{
if (entry.Entity is YourModel)
{
var proposedValues = entry.CurrentValues;
var databaseValues = entry.GetDatabaseValues();
foreach (var property in proposedValues.Properties)
{
var proposedValue = proposedValues[property];
var databaseValue = databaseValues[property];
// TODO: decide which value should be written to database
// proposedValues[property] = <value to be saved>;
}
// Refresh original values to bypass next concurrency check
entry.OriginalValues.SetValues(databaseValues);
}
else
{
throw new NotSupportedException(
"Don't know how to handle concurrency conflicts for "
+ entry.Metadata.Name);
}
}
}
}
}
请注意并发问题解决方案的灵活性和多功能性.
Notice the flexibility and versatility of the solution for the concurrency issue.
这篇关于实体框架-处理重复密钥的PK/UKC 2601违规的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
