linq to sql - cannot add an entity with a key that is already in use

Question

I have got this weird error Cannot add an entity with a key that is already in use But what is quite irritable about that error is that user gets no detais - Who? What? What table? What record is the culprit of this error?

It would be desperately complicated to determine it, in case you do many operations on LINQ objects before .Submit()

Is there any way to determine what certainly caused this error?

Answer 1

This error typically happens when you are creating a new record in a MetaTable with a foreign key relationship and the foreign key record already exists.

For example, let's say you have an Contact table and an Address table, and each Contact can hold multiple Addresses. The error occurs when you create a new Contact record and try to manually associate an existing Address record to that new Contact.

Assuming that the passed Address ID represents an existing Address record, this doesn't work:

public class Contact
{
    public int Contact_ID { get; set; }
    public string Name { get; set; }
    public Address ContactAddress { get; set; }
    public string Phone { get; set; }
}

public class Address
{
    public int Address_ID { get; set; }
    public string Street { get; set; }
    public string CityState { get; set; }
    public string ZIP { get; set; }
}

public void CreateNewContact(int addressID)
{
    Contact contact = new Contact();

    contact.Name = "Joe Blough";
    contact.ContactAddress.Address_ID = addressID;
    contact.Phone = "(555) 123-4567";

    DataContact.SubmitChanges();
}

Historically, SQL developers are trained to just pass the ID value in order for the magic to happen. With LINQ-to-SQL, because the database activity is abstracted, we have to pass the whole object so that the LINQ engine can properly reflect the necessary changes in the ChangeSet. In the above example, the LINQ engine assumes that you are asking to create a new Address record, because it didn't have one to work with when the SubmitChanges was made and it has to respect the contract established by the foreign key relationship. It creates a blank Address record with the passed ID value. The error occurs because that ID value already exists in the data table and the ChangeSet has not flagged the Address delta as an Update.

The fix is to pass in the entire record, not just the ID value:

contact.ContactAddress = DataContext.Addresses.Where(a => a.Address_ID == addressID).Single();

Now, the LINQ engine can properly flag the incoming Address record as an existing one and not try to recreate it.