Efcore: Why Are There Gaps in IDs?

[spottedmahn]

For some unknown reason my identities are jumping. Why could that be? Where do I start to look for why something like this happening?

Jump 1 Example

Jump 2 Example

Model

c# [Table("Customers", Schema = "Customers")] public class Customer : IValidatableObject { public int Id { get; set; } ... }

Table Generated from the Migration

``` sql
CREATE TABLE [Customers].[Customers] (
[Id] INT IDENTITY (1, 1) NOT NULL,
...
)

[smitpatel]

This stackoverflow questions answers it in detail.
http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database

It is the way SqlServer works (on restart) not something specific to EF behavior (which merely uses database values created by SqlServer)

[wstaelens]

@smitpatel Shouldn't this be reported as a bug? As this gives great danger, gaps can grow very quickly, reaching int.maxvalue without having so many items.

I just had the same issue and it affects all my tables now.

related: https://stackoverflow.com/questions/39671899/ids-jumping-up-1000-every-time-i-build-my-app-and-insert-a-new-record
related: https://stackoverflow.com/questions/21563320/why-identity-number-in-ef-jump-to-a-big-number

[smitpatel]

@wstaelens - While behavior can cause potential issue like that, the behavior is out of the hands of EF team. We just take SqlServer gives to us. So if you think this is a bug then it should be reported to SqlServer product.
From EF Core perspective, you can avoid hitting the issue but possibly not using identity. A user defined sequence or some client side value generation code or no value generation could work in EF Core.

[wstaelens]

@smitpatel this never happened to us before with regular EF and .NET but it happens now when I'm using EF Core and .NET Core. Strange.

[spottedmahn]

this never happened to us before with regular EF and .NET but it happens now when I'm using EF Core and .NET Core. Strange.

I'm guessing you're using Sql LocalDB now and previously you weren't. Maybe not, just a guess 😊.

Did you review the referenced SO post? @wstaelens

[wstaelens]

I'm using SQL Server 2014 Express with SSMS.
I assume I don't use LocalDB (as it wouldn't be visible in SSMS?)

[spottedmahn]

Gotcha, not sure then 🤷‍♂️. @wstaelens

But you can use SSMS and LocalDB:

[wstaelens]

it's not localdb ;-)
anyway very annoying these gaps.