无法使用已在使用的密钥添加实体 [英] cannot add an entity with a key that is already in use

问题描述

我遇到了这个奇怪的错误无法使用已在使用的密钥添加实体但是这个错误很让人恼火的是，用户没有得到任何细节——谁?什么?什么表?这个错误的罪魁祸首是什么记录?

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?

确定它会非常复杂，以防您在 .Submit()

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?

推荐答案

当您在具有外键关系的 MetaTable 中创建新记录并且外键记录已存在时，通常会发生此错误.

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.

例如，假设您有一个 Contact 表和一个 Address 表，并且每个 Contact 可以包含多个 Addresses.当您创建新的联系人记录并尝试将现有地址记录手动关联到该新联系人时，会发生该错误.

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.

假设传递的地址 ID 代表一个现有的地址记录，这是行不通的:

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 = "[phone]";

    DataContact.SubmitChanges();
}

从历史上看，SQL 开发人员接受过的培训只是传递 ID 值，以便魔术发生.使用 LINQ-to-SQL，因为数据库活动是抽象的，所以我们必须传递整个对象，以便 LINQ 引擎可以正确反映 ChangeSet 中的必要更改.在上面的示例中，LINQ 引擎假设您要求创建一个新的 Address 记录，因为在进行 SubmitChanges 时它没有可用的记录，并且它必须遵守由外键关系建立的契约.它使用传递的 ID 值创建一个空白地址记录.发生该错误的原因是该 ID 值已存在于数据表中，并且 ChangeSet 未将地址增量标记为更新.

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.

解决方法是传入整个记录，而不仅仅是 ID 值:

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

现在，LINQ 引擎可以正确地将传入的地址记录标记为现有记录，而不是尝试重新创建它.

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

这篇关于无法使用已在使用的密钥添加实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！