linq2sql：无法使用已在使用的密钥添加实体

I have a linq2sql setup where objects are sent from client side (flex via flourinefx) and attach them to a new datacontext a seen below:

我有一个linq2sql设置,其中对象从客户端发送(通过flourinefx flex)并将它们附加到新的datacontext,如下所示:

I also have a "global" datacontext that is used throughout the session.

我还有一个在整个会话期间使用的“全局”datacontext。

    public static void Update(Enquiry enquiry)
    {
        OffertaDataContext db = new OffertaDataContext();


        db.Enquiries.Attach(enquiry);
        db.Refresh(RefreshMode.KeepCurrentValues, enquiry);

        db.SubmitChanges();
    }

This approach usually works fine, but after a while I get the error "Cannot add an entity with a key that is already in use".

这种方法通常可以正常工作,但过了一段时间我得到错误“无法添加一个已经在使用的密钥的实体”。

11 个解决方案

#1

I think this error happens if you Attach an entity to a DataContext that was already loaded.

我认为如果您将实体附加到已加载的DataContext,则会发生此错误。

The code that causes the error is exactly like you show here? After creating the new OffertaDataContext do you query anything before the Attach?

导致错误的代码与您在此处显示的完全相同?在创建新的OffertaDataContext之后,您是否在Attach之前查询了什么?

#2

I was getting this error and it was because I had forgotten to set the Primary Key field in the database to "Identity Specification" (auto-increment). When I changed this I was good. Doh!

我收到此错误是因为我忘记将数据库中的主键字段设置为“标识规范”(自动增量)。当我改变这个时,我很好。卫生署!

#3

This might not be your issue (I can't tell), but it was mine and as people google this it might help someone else. If you aren't using the built-in Linq-to-SQL designer or SQLMetal stuff to generate your Linq-to-SQL classes, or if you forgot to make your ID column an IDENTITY, you may be missing a property on your column attribute called "IsDbGenerated". Be sure your column attribute looks something like this:

这可能不是你的问题(我无法分辨),但它是我的,而且人们谷歌这可能会帮助其他人。如果您没有使用内置的Linq-to-SQL设计器或SQLMetal来生成Linq-to-SQL类,或者如果您忘记将ID列设置为IDENTITY,则可能缺少列上的属性名为“IsDbGenerated”的属性。确保您的列属性看起来像这样:

<Column(Name:="ID", DbType:="Int NOT NULL IDENTITY", CanBeNull:=False, IsPrimaryKey:=True, IsDbGenerated:=True)>

#4

In case you inserting several entities at once, may you are just trying to insert a duplicate entity into current datacontext. I know this is too simple, but it just happened to myself.

如果您一次插入多个实体,可能只是尝试将重复的实体插入当前的datacontext。我知道这太简单了,但它恰好发生在我自己身上。

#5

Are you trying to add multiple new objects in one hit where the LinqEntities are being created with a key of 0?

您是否尝试在一个匹配中添加多个新对象,其中使用0的密钥创建LinqEntities?

I had this issue in the past when I was trying to add items into a table on my page and then when I tried to delete or update these items, multiples would have the key 0. So obviously it didn't know what to do with my requests...

我过去曾经遇到过这个问题,当时我试图将项目添加到我的页面上的表格中,然后当我尝试删除或更新这些项目时,倍数会有0键。所以显然它不知道该怎么办我的要求......

#6

This is what I've been doing to get around this error. Basically you find where this row is in the database based on a primary key. If it doesn't exist then you insert it. Otherwise you get the version from the database and update all of the necessary fields.

这就是我为解决这个错误所做的工作。基本上,您可以根据主键找到数据库中此行的位置。如果它不存在则插入它。否则,您将从数据库中获取版本并更新所有必需的字段。

public static void Update(Enquiry enquiry)
{
    JobsDataContext db = new JobsDataContext();

    var enquiries = from e in db.Enquiries
                    where e.PKID == enquiry.PKID
                    select e;

    if (enquiries.Count() < 1)
    {
        db.Enquiries.InsertOnSubmit(enquiry);
    }
    else
    {
        Enquiry updateEnquiry = enquiries.Single();

        updateEnquiry.LengthMm = enquiry.LengthMm;
        updateEnquiry.ShippedQty = enquiry.ShippedQty;
        updateEnquiry.StatusCode = enquiry.StatusCode;
    }

    db.SubmitChanges();
}

This can become tedious if you make updates to your database schema all the time, as you'll have to come back here to update the code.

如果您一直对数据库架构进行更新,这可能会变得乏味,因为您必须回到这里更新代码。

#7

I have a similar aproach to Noah's, but I use a stored procedure to verify if a record with that PK exists or not, this way the Entity is not loaded in the context and the update code only involves two lines of code and will not require changes in the future when you add/remove fields from the table, it will require to change the SP only if the PK of the table changes:

我对诺亚有类似的方法,但我使用存储过程来验证是否存在具有该PK的记录,这样实体不会在上下文中加载,更新代码只涉及两行代码而不需要将来从表中添加/删除字段时的更改,只有在表的PK更改时才需要更改SP:

bool existe = Convert.ToBoolean(dbc.spSConfigReportesPeriodicos(configReportesPeriodicos.CodigoCliente));

if (existe)
{
    dbc.ConfigReportesPeriodicos.Attach(configReportesPeriodicos);
    dbc.Refresh(RefreshMode.KeepCurrentValues, configReportesPeriodicos);
}
else
{
    dbc.ConfigReportesPeriodicos.InsertOnSubmit(configReportesPeriodicos);
}
dbc.SubmitChanges();

And here is the stored procedure:

这是存储过程:

ALTER PROCEDURE dbo.spSConfigReportesPeriodicos
(
    @codigoCliente int
)
AS

IF EXISTS(SELECT 1 FROM dbo.ConfigReportesPeriodicos WHERE CodigoCliente = @codigoCliente)
    RETURN 1
ELSE
    RETURN 0

RETURN

#8

You shouldn't have to do that kind of checking to see whether you need to use Updates or Inserts - that's for Linq to do!

您不应该进行这种检查,看看是否需要使用更新或插入 - 这是Linq要做的!

Here's an example from a project I'm working on (sorry it's in VB.Net :) ) which demonstrates how to solve this.

这是我正在研究的一个项目的例子(对不起,这是在VB.Net :)),它演示了如何解决这个问题。

The code is yet unoptimised and pretty ugly - but it gets the point across. You can ignore the bit where it pulls values from the checkboxlist - that just shows how you can update child entities.

代码尚未经过优化,非常难看 - 但它得到了重点。您可以忽略从复选框列表中提取值的位 - 它只显示如何更新子实体。

Here's the OnUpdating method, which encompasses the update (this is truncated code):

这是OnUpdating方法,它包含更新(这是截断的代码):

Protected Sub LinqDataSource22_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceUpdateEventArgs) Handles LinqDataSource22.Updating

            ' The main entity
            Dim updatedObject As FeedbackDraft = e.NewObject

            updatedObject.Modified = DateTime.Now
            updatedObject.ModifiedBy = UserHelper.GetCurrentUserName

            ' Example: Modify the updated object
            Dim aList As RadioButtonList = FeedbackFormView.FindControl("MyRadioButtonList")
            If aList IsNot Nothing AndAlso Not String.IsNullOrEmpty(aList.SelectedValue) Then
                updatedObject.aProperty = aList.SelectedValue
            End If


            ' Main context - for updating parent entity
            Using ctx As New CustomDataContext()

                ' Example: ... more modification of the main entity
                updatedObject.Status = "Draft"

                ' Deal with child items
                ' Secondary context - for checking against existing data in DB and removing items that have been unselected in the form
                Using ctx2 As New CustomDataContext()

                    ' We need to pull the record from the database to get the full constructed object graph
                    ' This method does a linq query to retrieve the FeedbackDraft object by ID
                    Dim originalObject As FeedbackDraft = GetOriginalFeedbackDraft(ctx2, updatedObject.FeedbackId)

                    ' ... truncated ...

                    ' Loop through CheckBoxList items and updated our entity graph
                    For Each li As ListItem In cbList.Items

                        ' ... code to work with ListItem truncated ...

                        Dim c As New ChildObject()
                        updatedObject.ChildObjects.Add(c)
                        ' Set the child collection to insert - this is using the main context
                        ctx.ChildObjects.InsertOnSubmit(c)


                        ' We can also delete things using the secondary context
                        Dim o as OtherChildObject()
                        o = GetOtherChildObjectById(updatedObject.FeedbackId)
                        ctx2.OtherChildObjects.DeleteOnSubmit(o)
                        ctx2.SubmitChanges()

                    Next
                End Using

                ' You can do further child object updates here...

                ' Now, attach main object for update
                ctx.PartnerFeedbackDrafts.Attach(updatedObject, e.OriginalObject)
                ctx.SubmitChanges()

            End Using

            e.Cancel = True

        End Sub

#9

try this even if your TEntity's (here Area) ID is an Identifier column; Just it, without any change in your SP or Model:

即使您的TEntity(此处为区域)ID是标识符列,也请尝试此操作;就是这样,您的SP或型号没有任何变化:

public void InitForm()
{
    bnsEntity.DataSource = CacheManagement.cachedAreas;
    newID = CacheManagement.cachedAreas.LastOrDefault().areaID + 1;
    grdEntity.DataSource = bnsEntity;
}

private void tsbNew_Click(object sender, EventArgs e)
{
    var newArea = new Area();
    newArea.areaID = (byte)newID++;
    dataContext.GetTable<Area>().InsertOnSubmit(newArea);
    bnsEntity.Add(newArea);
    grdEntity.MoveToNewRecord();
}

#10

I had this problem after selecting a row from the database with the primary key of say 'BOB'. Then i would truncate the table with dc.ExecuteCommand("TRUNCATE TABLE ShippingReport"); and do SubmitChanges(), thinking this would get rid of the field and I'd be able to insert another with the same key, but I got the OP's error when trying to insert. Just had to do dc = new DataContext(); after the first SubmitChanges and that fixed it for me, since that object still existed in the DataContext, which is basically what bruno conde's answer says.

从主数据库中选择一行说'BOB'后,我遇到了这个问题。然后我会用dc.ExecuteCommand(“TRUNCATE TABLE ShippingReport”)截断表格;并且做SubmitChanges(),认为这将摆脱该字段,我将能够插入另一个具有相同的键,但我尝试插入时得到OP的错误。只需要做dc = new DataContext();在第一次SubmitChanges之后并为我修复它,因为该对象仍然存在于DataContext中,这基本上就是bruno conde的答案所说的。

#11

In my case it happened in a situation where I had retrieved an entry, and then tried to update the entry with a new entry. Brainfart one might say, these things happens though. :p

在我的情况下,它发生在我检索了一个条目的情况下,然后尝试用新条目更新条目。 Brainfart有人可能会说,这些事情发生了。 :p

public void UpdateEntry(Entity entity)
{
    var oldEntry = select ....
    var updatedEntity = new Entity{...}; // mix of entity and oldEntry

    _repository.Update<Entity>(updatedEntity);
}

Becomes

public void UpdateEntry(Entity entity)
{
    var oldEntry = select ....

    oldEntry.CreationDate = entity.CreationDate {...}

    _repository.Update<Entity>(oldEntry);
}