c# - Error DuplicateKeyException when setting the id manually on Insert

Question

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];

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.

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.'.

The 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;
    }
}

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

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.

Answer 1

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

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.

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

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.