Why has AutoIncrement of Entity Id changed abnormally, and how to reset it? - ASP.NET Core, EF Core

Question

When new values are being added into my User table of my database, the entity id should be autonumerated, one more than the previously added entity. This was initially working fine, however, now any new entities added to this table has an UserId from 1014, while the last working entity had a UserId of 12.

I have not made changes to the code that adds new values into the table, with the only changes being made to the table is manually changing values of the field UserPass for some entities.

I was wondering whether there could be any explanation as to why this autoincrementing issue has occurred, with my second question being whether there would be any fix for this, such as reseting the autoincrement value for the User table.

My program is on ASP.NET 6.0 Core Web App, and I am using Entity Framework Core for managing the tables within my database.

I am a student with basic knowledge on ASP.NET Core and EF Core, and on how to solve this issue, therefore as much of a simplified explanation would be very appreciated.

Here is my code for my User.cs model:

namespace AQA_A_Level_CS_NEA__Suvat_Solver_.Models
{
    public class User
    {
        public int UserId { get; set; }
        public string UserName { get; set; } = string.Empty;
        public string UserPass { get; set; } = string.Empty;
        public int UserCorrectAnsw { get; set; } = 0;
        public int UserTotalAnsw { get; set; } = 0;

        public List<UsertoCourses> UsertoCourses { get; set; }


    }
}

Here is the code for the Migration from the User.cs model:

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace AQA_A_Level_CS_NEA__Suvat_Solver_.Migrations
{
    public partial class UserCourseRelation : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "UsertoCourses",
                columns: table => new
                {
                    UserId = table.Column<int>(type: "int", nullable: false),
                    CourseId = table.Column<int>(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_UsertoCourses", x => new { x.UserId, x.CourseId });
                    table.ForeignKey(
                        name: "FK_UsertoCourses_Courses_CourseId",
                        column: x => x.CourseId,
                        principalTable: "Courses",
                        principalColumn: "CourseId",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_UsertoCourses_User_UserId",
                        column: x => x.UserId,
                        principalTable: "User",
                        principalColumn: "UserId",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_UsertoCourses_CourseId",
                table: "UsertoCourses",
                column: "CourseId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "UsertoCourses");
        }
    }
}

Here is my code for ApplicationDbContext.cs:

namespace AQA_A_Level_CS_NEA__Suvat_Solver_.Data
{
    using Microsoft.EntityFrameworkCore;
    using AQA_A_Level_CS_NEA__Suvat_Solver_.Models;
    public class ApplicationDbContext : DbContext
    {

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Question>()
            .HasKey(c => c.QuestId);
            modelBuilder.Entity<UsertoCourses>()
            .HasKey(c => new { c.UserId, c.CourseId });
            modelBuilder.Entity<QuestiontoCourses>()
            .HasKey(c => new { c.QuestId, c.CourseId });
        }

        public DbSet<User> User { get; set; }
        public DbSet<Course> Courses { get; set; }
        public DbSet<Question> Question { get; set; }
        public DbSet<UsertoCourses> UsertoCourses { get; set; }
        public DbSet<QuestiontoCourses> QuestiontoCourses { get; set; }
    }
}

Many Thanks.

Answer 1

This typically happens where a number of rows have been added by some means then deleted. Deleting rows, even off the "Tail" of a table doesn't reset auto-increment sequences. Sequences, such as SQL Server's Identity can typically be reseeded if you're doing a maintenance event and want to bring the seed back down to a particular point. (I.e. if you have a data import that you want to roll back, deleting those records and restoring the seed)

For SQL Server this is:

DBCC CHECKIDENT ('User', REESEED, 13)

to resume inserting ID's at 13, 14, etc.

Generally though for meaningless PKs the Identity value should not matter, just that it is unique and indexed. Using Identity columns offers the best performance but they are guess-able so you need to account for that if you are in any way exposing those PKs in a way that users could see or potentially manipulate them. For example on the URL of a web request, or passing IDs as part of Form data. UserId 12 could be substituted with UserId 11 on a POST.

Meaningful keys should not use Identity columns. These are often things that business logic depends upon and might be associated with things like Enumerations in the code base, and used for conditional logic.

Thoughts on GUIDs:

For values that could be inspected and submitted via form data and such where you want to better detect and deter tampering, GUIDs are a good option, but I would avoid using these as PKs & FKs strictly because the fragmentation that GUIDs can cause in indexes, particularly clustered indexes. Sequential GUIDs can help mitigate this, but somewhat defeat the purpose of allowing for non-guessable values. Where Sequential GUID PKs are justified are distributed database systems where records can be inserted in one of several database copies and replicated to others.

Instead, keep all PK & FK relationships as Identity columns and append an indexed (non-clustered) GUID column to serve as a searchable "Key". These keys are what you can send on the URL or in Form Data and use to locate actual PKs, while still maintaining the performance benefits of clustered indexes for reading related data via FKs to the PK.

I.e. instead of GET /Users/12 resolving to:

return _context.Users.Single(x => x.UserId == userId)
    .ProjectTo<UserViewModel>(config);

GET /Users/c3b03d9d-cf57-4dca-a060-6a2ea827d672 (example GUID, dash syntax for example, usually use no-dash)

return _context.Users.Single(x => x.UserKey == userKey);
    .ProjectTo<UserViewModel>(config);

User still has a UserId PK of 12, which is what every other entity references as the FK to that user, but also has a UserKey GUID which would be the revealed Key for that user. View models sent to the UI would never reveal the PK or FKs, only the Keys. In this way the PKs remain truly meaningless and protected.