在插入时手动设置ID时发生错误DuplicateKeyException [英] Error DuplicateKeyException when setting the id manually on Insert

问题描述

我有一个表，该表根据序列生成其主键:

I have a table which generates its primary key from a sequence:

CREATE SEQUENCE [dbo].[seq_PK_testTable] AS [int] START WITH 0 INCREMENT BY 1;
CREATE TABLE [dbo].[testTable](
    [id] [int] NOT NULL,
 CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED ([id] ASC)) ON [PRIMARY];
ALTER TABLE [dbo].[testTable] ADD CONSTRAINT [DF_testTable_id] DEFAULT (NEXT VALUE FOR [seq_PK_testTable]) FOR [id];

为了能够使用此密钥生成机制，我选择使用数据上下文的Insert部分方法，如

In order to be able to work with this key generation mechanism, I've chosen to use the Insert partial method of the data context, as proposed in this answer.

对于第一个插入，它就像一个魅力:执行插入并在对象中更新ID，但是一旦我插入第二个对象，我就会得到一个System.Data.Linq.DuplicateKeyException: 'Cannot add an entity with a key that is already in use.'.

For the first insert it works like a charm: the insert is performed and the ID is updated in the object, but as soon as I insert a second object I get a System.Data.Linq.DuplicateKeyException: 'Cannot add an entity with a key that is already in use.'.

MCVE :

using System.Data.Linq.Mapping;

namespace test
{
    static class Program
    {
        static void Main(string[] args)
        {
            var db = new DataClasses1DataContext(@"Data Source=");
            var testTableRecord1 = new testTable();
            var testTableRecord2 = new testTable();
            db.GetTable<testTable>().InsertOnSubmit(testTableRecord1);
            db.SubmitChanges();
            db.GetTable<testTable>().InsertOnSubmit(testTableRecord2);
            db.SubmitChanges();
        }
    }

    [Database(Name = "TestDB")]
    public class DataClasses1DataContext : System.Data.Linq.DataContext
    {
        public DataClasses1DataContext(string fileOrServerOrConnection) : base(fileOrServerOrConnection) { }

        void InserttestTable(testTable instance)
        {
            using(var cmd = Connection.CreateCommand())
            {
                cmd.CommandText = "SELECT NEXT VALUE FOR [dbo].[seq_PK_testTable] as NextId";
                cmd.Transaction = Transaction;
                instance.id = (int)cmd.ExecuteScalar();
                ExecuteDynamicInsert(instance);
            }
        }
    }

    [Table(Name = "dbo.testTable")]
    public class testTable
    {
        [Column(DbType = "Int NOT NULL", IsPrimaryKey = true)]
        public int id;
    }
}

我认为它内部仍然希望id为0.如何强制LINQ反映真实ID?

I assume it internally still expects the id to be 0. How can I force LINQ to reflect the real id?

PS:因为该问题已被标记为> LINQ to SQL的重复项插入主键索引:IsPrimaryKey = true和IsDbGenerated = true不起作用，因为它们导致LINQ to SQL生成查询IDENTITY ID(即SELECT CONVERT(Int,SCOPE_IDENTITY()))的代码，如果返回NULL，则返回NULL ID已创建，默认值为SEQUENCE.

PS: because the question has been flagged as a duplicate of LINQ to SQL insert primary key index: IsPrimaryKey = true and IsDbGenerated = true doesn't work because they cause LINQ to SQL to generate code that queries for an IDENTITY ID, i.e. SELECT CONVERT(Int,SCOPE_IDENTITY()) and that returns NULL if the ID has been created with the default value of a SEQUENCE.

推荐答案

您可以通过为每个插入操作实例化DataContext的新实例来解决此问题.

You can work around this by instantiating a new instance of the DataContext for each insert operation.

var db = new DataClasses1DataContext(@"Data Source=");   
var testTableRecord1 = new testTable();
db.GetTable<testTable>().InsertOnSubmit(testTableRecord1);
db.SubmitChanges();

db = new DataClasses1DataContext(@"Data Source=");    
var testTableRecord2 = new testTable();
db.GetTable<testTable>().InsertOnSubmit(testTableRecord2);
db.SubmitChanges();

如果您想了解正在发生的事情，请尝试在断言的InsertTestTable方法中放置一个断点.您将看到在第二次调用SubmitChanges()之后没有调用它.数据上下文的每个实例都维护一个高速缓存，其中包含插入，更新或检索的每个实体.缓存的行为就像字典一样，使用实体的主键作为字典键.由于某种原因，LINQ对每个缓存的实体仅运行一次自定义插入逻辑. IMO，这是LINQ中的错误，我找不到任何证明这种行为的文档.

If you want to understand what is happening, try putting a breakpoint inside the parital InsertTestTable method. You'll see that it isn't getting called after the second call to SubmitChanges(). Each instance of the data context maintains a cache containing every entity that is inserted, updated, or retrieved. The cache behaves like a dictionary using the primary key of the entity as the dictionary key. For some reason, LINQ is only running the custom insert logic once per cached entity. IMO this is a bug in LINQ, and I cannot find any documentation that justifies this behavior.

要了解我的意思，请尝试将每个实体的ID设置为单独的值，然后您会看到自定义插入行为实际上可以正常工作:

To understand what I mean, try setting the id of each entity to a separate value, and you'll see that the custom insert behavior actually works correctly:

var db = new DataClasses1DataContext(@"Data Source=");   
var testTableRecord1 = new testTable(){ id = -1 };
var testTableRecord2 = new testTable(){ id = -2 };
db.GetTable<testTable>().InsertOnSubmit(testTableRecord1);
db.SubmitChanges();
db.GetTable<testTable>().InsertOnSubmit(testTableRecord2);
db.SubmitChanges();

我的建议是在每次调用SubmitChanges()之前创建数据上下文的新实例，或者将插入的内容批处理为单个SubmitChanges(如果可能的话，这是最好的方法).使用LINQ时，通常应将数据上下文视为短命的一次性对象.

My advice is to create a new instance of the data context before each call to SubmitChanges(), OR batch your inserts to a single SubmitChanges (this is best, if possible). When using LINQ, the data context should generally be treated as a short-lived, disposable object.

这篇关于在插入时手动设置ID时发生错误DuplicateKeyException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！