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?

以下是代码的精简版本:

Here's the trimmed down version of the code:

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

>: //social.msdn.microsoft.com/forums/zh-CN/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屋！