EF6 throws concurrency exception when trying to add new entry with identity column-CodePudding

I'm generating entity models from my database with EF6. I created two test tables. One table has an Identity column, and the other table doesn't. Here are the tables:

CREATE TABLE [dbo].[TestNoIdentity] 
(
    [ID]      INT          NOT NULL,
    [DTStamp] DATETIME     NOT NULL,
    [Note]    VARCHAR(255) NULL,

    PRIMARY KEY CLUSTERED ([ID] ASC, [DTStamp] ASC)
);

CREATE TABLE [dbo].[TestIdentity] 
(
    [ID]      INT IDENTITY (1, 1) NOT NULL,
    [DTStamp] DATETIME     NOT NULL,
    [Note]    VARCHAR(255) NULL,

    PRIMARY KEY CLUSTERED ([ID] ASC, [DTStamp] ASC)
);

Test code:

using (TestEntities entities = new TestEntities())
{
    // This works
    var entry1 = new TestNoIdentity();
    entry1.ID = 1;
    entry1.DTStamp = DateTime.Now;
    entry1.Note = "No Identity";
    entities.TestNoIdentity.Add(entry1);
    entities.SaveChanges();

    // This doesn't work
    var entry2 = new TestIdentity();
    entry2.DTStamp = DateTime.Now;
    entities.TestIdentity.Add(entry2);
    entities.SaveChanges();     //optimistic concurrency exception
    
    // This query works
    // entities.Database.ExecuteSqlCommand("INSERT INTO [dbo].[TestIdentity] ([DTStamp]) VALUES ('1/1/2021 12:00:00 PM')");
    
    return entities.ID.ToString();
}

Why is it throwing a concurrency exception? There are no other users or duplicated instances of the entity.

The message from the exception:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded.

CodePudding user response：

Without IDENTITY EF doesn't have to fetch back the ID, and that's where it's failing. You've got a DATETIME column in your PK, and DATETIME only has precision of about 3ms, so comparing the stored value with the generated value may fail. Change it to DATETIME2 to better match the precision of .NET's DateTime, or trim your .NET DateTime to the nearest second.