LINQ to SQL - DuplicateKeyException during update

Question

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

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

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 = "[email protected]";
// 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
// ...   

Answer 1

Make this change:

customerTable.Attach(customer, existing);

^ I am not sure why the above wouldn't work. The second argument is the "original state" of the entity, perhaps because it's a different reference to a different instance, L2S thinks it needs to insert a whole new object.

I think it would be better to do something like:

var customer = ctx.Where(...).SingleOrDefault();
if (customer == null)
{
  customer = new Customer()
  {
    Name = name,
    Email = email
  };
  customerTable.InsertOnSubmit(customer);
}
else
{
  customer.Name = name;
  customer.Email = email;
}

ctx.SubmitChanges();

Answer 2

I am new to LINQ to SQL, so if someone smarter than me sees this is wrong, please correct me. But, I believe your problem is that when you get into the if statement, you are getting the entity from the results (via results.Single()) and you are setting the value to the NEW customer object. When you attempt to submit the customer object to the database, the primary key already exists, so you receive the error.

Instead, you want to update the existing customer and submit that back to the database.

Answer 3

The way that I do this is the following

I would pull the customer that already exists like you do then update the item with the matching id that you are using, with the values you've pulled from xml. Then when you call you datacontext.SubmitChanges() method it will do the update for you.

Or you could use the Attach Method, in your case your code would look something like

customerTable.Attach(customer, existing);

Attach was created exactly for this case.

Edit

Why don't you change the order you are doing things in, instead of making a new customer, populating that customer, Do something like this

var results = ctx.Where(c => c.Email == customer.Email);

Customer customer = (results.Any ? results.Single : new Customer)

Then query your xml to fill the customer then do your insert/update.

Answer 4

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/

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 = "[email protected]";
// 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
// ...  

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.