EF Core 6 - Auto increment Identity primary key problem

Question

Hi,

I have had a Blazor Server application running on production for a while. There is an Orders table, user can insert records.

USE [InventoryManagement]  
GO  
  
/****** Object:  Table [dbo].[Orders]    Script Date: 9.01.2023 14:37:29 ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE TABLE [dbo].[Orders](  
 [Id] [int] IDENTITY(1,1) NOT NULL,  
 [OrderDateTime] [datetime2](7) NOT NULL,  
 [Status] [nvarchar](max) NOT NULL,  
 [DoneBy] [nvarchar](50) NOT NULL,  
 [CustomerId] [int] NOT NULL,  
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  
  
ALTER TABLE [dbo].[Orders] ADD  DEFAULT ((0)) FOR [CustomerId]  
GO  
  
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers_CustomerId] FOREIGN KEY([CustomerId])  
REFERENCES [dbo].[Customers] ([Id])  
ON DELETE CASCADE  
GO  
  
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers_CustomerId]  
GO  

The Id column is the primary key and auto-increment by 1.

The problem is, this Id column increased one by one for a while, then suddenly became 1100. I checked the logs, and here is the insert statement.

2022-12-21 11:06:16.9792|0|DEBUG|Microsoft.EntityFrameworkCore.ChangeTracking|DetectChanges starting for 'IMSContext'.   
2022-12-21 11:06:16.9792|0|DEBUG|Microsoft.EntityFrameworkCore.ChangeTracking|DetectChanges completed for 'IMSContext'.   
2022-12-21 11:06:17.0001|0|DEBUG|Microsoft.EntityFrameworkCore.Database.Connection|Opening connection to database 'InventoryManagement' on server '.\SQLEXPRESS'.   
2022-12-21 11:06:17.0001|0|DEBUG|Microsoft.EntityFrameworkCore.Database.Connection|Opened connection to database 'InventoryManagement' on server '.\SQLEXPRESS'.   
2022-12-21 11:06:17.0001|0|DEBUG|Microsoft.EntityFrameworkCore.Database.Transaction|Beginning transaction with isolation level 'Unspecified'.   
2022-12-21 11:06:17.0001|0|DEBUG|Microsoft.EntityFrameworkCore.Database.Transaction|Began transaction with isolation level 'ReadCommitted'.   
2022-12-21 11:06:17.0305|0|DEBUG|Microsoft.EntityFrameworkCore.Database.Command|Creating DbCommand for 'ExecuteReader'.   
2022-12-21 11:06:17.0305|0|DEBUG|Microsoft.EntityFrameworkCore.Database.Command|Created DbCommand for 'ExecuteReader' (0ms).   
2022-12-21 11:06:17.0305|0|DEBUG|Microsoft.EntityFrameworkCore.Database.Command|Executing DbCommand [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 50), @p2='?' (DbType = DateTime2), @p3='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']  
SET NOCOUNT ON;  
INSERT INTO [Orders] ([CustomerId], [DoneBy], [OrderDateTime], [Status])  
VALUES (@p0, @p1, @p2, @p3);  
SELECT [Id]  
FROM [Orders]  
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();   
2022-12-21 11:06:17.0305|0|INFO|Microsoft.EntityFrameworkCore.Database.Command|Executed DbCommand (2ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 50), @p2='?' (DbType = DateTime2), @p3='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']  
SET NOCOUNT ON;  
INSERT INTO [Orders] ([CustomerId], [DoneBy], [OrderDateTime], [Status])  
VALUES (@p0, @p1, @p2, @p3);  
SELECT [Id]  
FROM [Orders]  
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();   
2022-12-21 16:06:46.6661|0|INFO|Microsoft.EntityFrameworkCore.Database.Command|Executed DbCommand (1ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 50), @p2='?' (DbType = DateTime2), @p3='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']  
SET NOCOUNT ON;  
INSERT INTO [Orders] ([CustomerId], [DoneBy], [OrderDateTime], [Status])  
VALUES (@p0, @p1, @p2, @p3);  
SELECT [Id]  
FROM [Orders]  
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();   
2022-12-21 16:06:46.6661|0|DEBUG|Microsoft.EntityFrameworkCore.ChangeTracking|The foreign key property 'Order.Id' was detected as changed. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see property values.   

Here is the Order entitiy:

public class Order  
    {  
        public int Id { get; set; }  
          
        [Required]  
        public DateTime OrderDateTime { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public int CustomerId { get; set; }  
        public string Status { get; set; }  
        [MaxLength(50)]  
        public string DoneBy { get; set; }  
        public List<OrderDetail> OrderDetails { get; set; }  
        public Customer Customer { get; set; }  
  
  
    }  

Is this a bug? What is the reason that Id did not increment 1 by 1?

Thank you.

Answer 1

most likely the jump is due to a transaction rollback (probably a bulk insert).

the identity value is not guaranteed to not have gaps. a transaction can update the identity value, but if it rollsback, then the identity value is not restored.