0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SimonJ9
  • 17
  • 4
  • Please check this post: https://stackoverflow.com/questions/1836173/entity-framework-store-update-insert-or-delete-statement-affected-an-unexpec – José Polanco Sep 07 '21 at 18:53
  • @José Polanco Hi, I have checked that post and several other related threads. I can update the table with DbContext.Database.ExecuteSqlCommand(), and the other table with the same properties except for the identity column can be updated without issues. I can't figure out the reason why it is throwing the concurrency exception when there is no other code modifying the entities/database. – SimonJ9 Sep 07 '21 at 19:26

1 Answers1

2

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67