How to set Identity Seed value in code-first?

Question

We are using Code First with EF-core and I would like to add a column which has an Identity Seed starting at another value other to 1.

Currently we can set it to auto increment via the EntityTypeBuilder during migrations using:

entityBuilder.Property(e => e.PropertyName).ValueGeneratedOnAdd();

However I cannot find out how to change the identity seed. Does it still need to be updated like it was with other versions of EF? e.g. writing some custom sql and running this during migration?

How to seed identity seed value in entity framework code first for several tables

How do I set Identity seed on an ID column using Entity Framework 4 code first with SQL Compact 4?

In EF-core there does not seem to be code for SqlServerMigrationSqlGenerator > override Generate(AlterTableOperation alterTableOperation)?

score 11 · Answer 1 · answered Sep 28 '20 at 11:07

Update 2020.

After EF Core 3.0 now you have a UseIdentityColumn extension method which can be used for setting the seed and increment values for identity columns.

builder.Property(prop => prop.Id)
            .UseIdentityColumn(10000000, 1);

As per offcial documentation:

UseIdentityColumn Configures the key property to use the SQL Server IDENTITY feature to generate values for new entities, when targeting SQL Server. This method sets the property to be OnAdd.

Link

score 7 · Accepted Answer · answered Feb 03 '18 at 08:46

In Entity Framework Core Use Sql Command in Up method:

Important Part: migrationBuilder.Sql("DBCC CHECKIDENT ('Payment', RESEED, 1000000)");

using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using System;

namespace PaymentService.Migrations
{
    public partial class Initial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
            name: "Payment",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                          .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Payment", x => x.Id);
            });

            // Below code is for seeding the identity
            migrationBuilder.Sql("DBCC CHECKIDENT ('Payment', RESEED, 1000000)");
        }

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

score 0 · Answer 3 · answered Jan 21 '16 at 16:56

0

Yes, you must write the required SQL statement to set the seed, and then use the Sql method in the migration.

answered Jan 21 '16 at 16:56

ErikEJ

40,951
5
75
115

Thanks. Do you know of any documentation for this? – Tim B James Jan 21 '16 at 20:53

score 0 · Answer 4 · answered Aug 02 '21 at 10:17

If you're using MySQL instead of SQL Server, add the following in place of "migrationBuilder.Sql("DBCC CHECKIDENT ('Payment', RESEED, 1000000)");" from the first answer:

migrationBuilder.Sql("use db_name; ALTER TABLE Clients AUTO_INCREMENT = start_value;");

Biswarekha · Answer 5 · 2022-07-06T09:55:44.800

-1

In migrationBuilder.CreateTable I have set Id to Identity column

migrationBuilder.CreateTable(
                name: "tblEmployees",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false).Annotation("SqlServer:Identity", "1, 1"),//for set identity column
                    EmployeeCode = table.Column<string>(nullable: false),
                    Name = table.Column<string>(maxLength: 100, nullable: false)
 },
                constraints: table =>
                {
                    table.PrimaryKey("PK_tblEmployees", x => x.Id);
                });

It worked for me

edited Jul 06 '22 at 09:55

answered Jul 06 '22 at 09:52

Biswarekha

1
1

2

Doesn't answer the question "However I cannot find out how to change the identity seed." I think other answers address this question sufficiently, no need to add anything to it now. – Gert Arnold Jul 06 '22 at 10:00

How to set Identity Seed value in code-first?

5 Answers5

Linked

Related