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

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 2

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();