LINQ to SQL - 更新期间出现 DuplicateKeyException [英] LINQ to SQL - DuplicateKeyException during update

问题描述

下面列出的代码尝试更新数据库中的一行，但抛出异常:

The code listed below attempts to update a row in the database, but throws an exception instead:

System.Data.Linq.DuplicateKeyException:无法添加具有以下键的实体已经在使用中

System.Data.Linq.DuplicateKeyException: Cannot add an entity with a key that is already in use

我见过的大多数示例都查询数据库以获取实体的实例，修改实例的一些属性，然后更新它.在这里，我完全从不同的来源获取对象(它是从 XML 文件解析的)并查询是否已经有一行用于此数据.如果有，我正在设置主键并尝试运行更新.这样做的正确方法是什么?

Most examples I've seen query the database to obtain an instance of an entity, modify some of the instance's properties, and then update it. Here I'm getting the object from a different source entirely (it's being parsed from an XML file) and querying to see if there's already a row for this data. If there is, I'm setting the primary key and attempting to run an update. What's the correct way to do this?

这是代码的精简版:

Customer customer = new Customer(); // Customer has a database generated
                                    // identity column called CustomerId

// populate customer object
customer.Name = "Mr. X";
customer.Email = "[e-mail]";
// etc.

// is customer already in database?
// identify customer by email
var results = ctx.Where(c => c.Email == customer.Email); // ctx is a DataContext

if (results.Any())
{
   Customer existing = results.Single();

   // set primary key to match existing one
   customer.CustomerId = existing.CustomerId;

   // update database
   customerTable.Attach(customer);  // customerTable is a Table<Customer>
   ctx.SubmitChanges();
}

// otherwise do insert
// ...   

推荐答案

显然这不是一个新问题.以下是讨论此问题的一些帖子的示例:

Apparently this is not a new problem. Here's a sampling of some of the posts that discuss this issue:

http://www.west-wind.com/weblog/posts/134095.aspx

http://www.codeproject.com/KB/linq/linq-to-sql-detach.aspx

http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/3848c02c-464e-40ff-87b6-813bff7b1263/

我通过在更新之前创建一个新的 DataContext 和 Table 来让它工作.我修改后的代码如下所示:

I got it working by creating a new DataContext and Table before doing the update. My modified code looks like this:

Customer customer = new Customer(); // Customer has a database generated
                                    // identity column called CustomerId

// populate customer object
customer.Name = "Mr. X";
customer.Email = "[e-mail]";
// etc.

// is customer already in database?
// identify customer by email
var results = ctx.Where(c => c.Email == customer.Email); // ctx is a DataContext

if (results.Any())
{
   Customer existing = results.Single();

   // set primary key to match existing one
   customer.CustomerId = existing.CustomerId;

   // **** CODE CHANGES HERE ****
   // create new DataContext and table to avoid DuplicateKeyException errors
   var ctx = new DataContext(customerTable.Context.Connection.ConnectionString);
   customerTable = ctx.GetTable<Customer>();

   // update database
   customerTable.Attach(customer);  // customerTable is a Table<Customer>

   // **** ANOTHER CODE CHANGE ****
   // without this line the data won't be updated with the new values
   ctx.Refresh(RefreshMode.KeepCurrentValues, customer);

   ctx.SubmitChanges();
}

// otherwise do insert
// ...  

我的理解是，DataContext 只能包含每个唯一实体的一个实例.尝试附加具有相同主键的新实体会导致错误，因为现在将有同一实体的两个实例.新的 DataContext 不知道现有实体，因此附加新实体没有问题.

The way I understand this is that the DataContext can only contain one instance of each unique entity. Attempting to attach a new entity with the same primary key causes the error, as there will now be two instances of the same entity. The new DataContext does not know about the existing entity and so has no problem attaching the new one.

更新:看起来像这个问题 已经回答.

UPDATE: It looks like this question has already been answered.

更新:不要按原样使用我的示例代码.它给我带来了其他问题.

UPDATE: Don't use my sample code as-is. It caused me other problems.

这篇关于LINQ to SQL - 更新期间出现 DuplicateKeyException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！