EF Core - Identity field suddenly jumped by 1000 and not able to search with Id

Question

I am having a problem with primary key/Id of one table in a project. In this table, the Id suddenly jumped from 38 to 1039.

Now the real issue is, when i find the entity by 1039 it doesn't exists but finding it by 39 gives me the entity. I am not sure about this behaviour and hence not able to find the solution.

My model

public class Domain : Entity
{
        public string Name { get; set; }
        public string Description { get; set; }
}


public abstract class Entity
{

  public int Id { get; set; }

    public DateTime InsertDate { get; set; }

    public DateTime? UpdateDate { get; set; }

    public DateTime? DeleteDate { get; set; }

    public bool IsDeleted { get; set; }
}

Method is like this...

public async Task<Response> Delete(int id)
{
            var domain = await DataContext.Domains.FindAsync(id);

            if (domain == null)
            {
                return new Response(ResponseType.NotFound);
            }
}

Can anyone please help ?

You didn't provide much information... First, you shouldn't ever rely on autogenerated/auto-incremented values in your logic. Second, the database in the background will generate such values and some databases will increment it, when an entity was added but later removed (due to transaction rollback). i.e. MySQL: if you add something that violates a unique constraint, then the process will be rolled back by my MySQL, but the auto-increment field will still be increased — Tseng, Jan 19 '18 at 07:01
Just for clarification, is the value in the database table 39 or 1039? Or is this only in your code. — , Jan 19 '18 at 07:55
@Tseng i would like to hear your suggestions. I am using Sql Server. I am adding some more info. — Faizal Shap, Jan 19 '18 at 10:45
By witch field are you searching? Post at least your model, the linq queries you used and the exact data as its stored in the database. We don't even know if you have two fields or not or how you call it. Typically you should not mind if the unique id is sequential with or without gaps, its just stuff that database needs to manage relations and typically has no meaning in your business logic — Tseng, Jan 19 '18 at 10:49
Ok i have added some info. I hope it makes some sense now :) — Faizal Shap, Jan 19 '18 at 10:58
Possible duplicate of [Identity increment is jumping in SQL Server database](https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database) — juunas, Jan 19 '18 at 11:30

score 8 · Accepted Answer · answered Jul 19 '20 at 10:27

it depend from Database setting IDENTITY-CACHE.

Identity cache store some values of a identity columns in case of SQL CRASH during a transaction or similar. To avoid gaps in an identity column, you need to set IDENTITY-CACHE to OFF running this command on a SQL query window:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

you will find more informations here:

https://social.technet.microsoft.com/wiki/contents/articles/40666.sql-server-2017-identity-cache-feature.aspx

EF Core - Identity field suddenly jumped by 1000 and not able to search with Id

1 Answers1