C# linq2sql：无法使用已在使用的密钥添加实体
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/420718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
linq2sql: Cannot add an entity with a key that is already in use
提问by Niels Bosma
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 设置，其中对象是从客户端发送的（flex 通过fluorinefx）并将它们附加到一个新的数据上下文，如下所示：
I also have a "global" datacontext that is used throughout the session.
我还有一个在整个会话中使用的“全局”数据上下文。
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".
这种方法通常工作正常，但一段时间后我收到错误消息“无法使用已在使用的密钥添加实体”。
采纳答案by bruno conde
I think this error happens if you Attachan entityto a DataContextthat was already loaded.
我认为如果您已经加载了Attach一个实体，则会发生此错误DataContext。
The code that causes the error is exactly like you show here? After creating the new OffertaDataContextdo you query anything before the Attach?
导致错误的代码与您在此处显示的完全一样？创建新后OffertaDataContext，您是否在Attach?之前查询任何内容？
回答by BenAlabaster
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。所以显然它不知道如何处理我的要求...
回答by Noah
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.
如果您一直更新数据库架构，这可能会变得乏味，因为您必须返回此处更新代码。
回答by Bardaan
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();
}
回答by Manuel Castro
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
回答by Joe Niland
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
回答by mattmc3
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)>
回答by vealer
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!
我收到此错误，这是因为我忘记将数据库中的主键字段设置为“身份规范”（自动增量）。当我改变这个时，我很好。哦！
回答by Julio Nobre
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.
如果您一次插入多个实体，您可能只是想将重复的实体插入到当前数据上下文中。我知道这太简单了，但这只是发生在我自己身上。
回答by Mikey G
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");和 do截断表SubmitChanges()，认为这会摆脱该字段，并且我可以使用相同的键插入另一个，但是在尝试插入时遇到了 OP 的错误。只需要dc = new DataContext();在第一个 SubmitChanges 之后做，并且为我修复了它，因为该对象仍然存在于 DataContext 中，这基本上就是 bruno conde 的回答所说的。
