37

I have a entity class

public class Employee
{
    public long Id { get; set; }
    public string Name { get; set; }
}

I have set the Id field as the primary key with auto number generation

modelBuilder.Entity<Employee>().HasKey(e => e.Id);
modelBuilder.Entity<Employee>().Property(e => e.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

But I want the Identity to seed from 10000 instead of from 1 which is the default. How can I specify this in EF?

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
Achinth Gurkhi
  • 2,136
  • 4
  • 24
  • 45
  • You must specify it when you are creating table – Saleh May 12 '11 at 07:15
  • 2
    @LIghtWing EF Code first creates the table automatically for you. The question is how to get EF to set the seed value for you when the table is created, not how to do it manually. – Kyle Trauberman May 12 '11 at 07:18
  • Oh you want EF create ur table.ok i see. – Saleh May 12 '11 at 07:19
  • This is a bit of a pest isn't it. Hopefully, at some point, you'll be able to specify the seed value perhaps as an overload to the HasDatabaseGeneratedOption method instead of having to step outside what is otherwise a very good framework. – mattpm Dec 13 '16 at 23:15
  • Does this answer your question? [How do I set Identity seed on an ID column using Entity Framework 4 code first with SQL Compact 4?](https://stackoverflow.com/questions/11732102/how-do-i-set-identity-seed-on-an-id-column-using-entity-framework-4-code-first-w) – OzBob Nov 17 '22 at 02:54

4 Answers4

35

If you are using SQL Server you must create custom database initializer and manually execute DBCC CHECKIDENT ('TableName', RESEED, NewSeedValue). For creating and using custom initializer with custom SQL commands check this answer.

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 7
    Code First, Database Specific SQL Last ;) – Baldy May 12 '11 at 07:31
  • 10
    Code first is based on the fact that you have absolutely no requirements on database. If you have requirements you must introduce database specific SQL. – Ladislav Mrnka May 12 '11 at 07:32
  • If you put this in a seeder, that seeder will run on each initialization. Then you’ll reset the value each time unless you manually calculate it. This leads to a constraint error on your next `INSERT`. – binki Feb 15 '19 at 16:21
20

Based on Ladislav Mrnka's answer, you could add this in your migration file Up method:

Sql("DBCC CHECKIDENT ('TableName', RESEED, NewSeedValue)");
Ehsan88
  • 3,569
  • 5
  • 29
  • 52
  • 1
    Thank you for the solution working with VS only instead of SQL Server that requires more deployment efforts. – Thomas.Benz Nov 16 '16 at 19:57
  • 2
    IMHO - This should be the accepted answer. It's the more 'Code First' way to do it. Put all the database construction logic into the migrations so there's nothing to forget when you rebuild the db days/weeks/months later. – Mike Devenney Jul 19 '17 at 12:51
  • And it is easier as a migration because that will only ever get run once. If you accidentally seed the column multiple times (e.g., by putting it in a data seeder/initializer instead of a migration), you’ll get an ID collision on your next `INSERT`. – binki Feb 15 '19 at 16:18
1

based on @ehsan88 you can make a Database Initialize class 

 public class AccDatabaseInitializer : CreateDatabaseIfNotExists<YourContect>
    {
      protected override void Seed(YourContect context)
       {
          context.Database.ExecuteSqlCommand("DBCC CHECKIDENT ('TableName', RESEED, NewSeedValue)");
           context.SaveChanges();
       }
    }

thanks

kareem khalil
  • 29
  • 2
  • 8
0

You can defninitely achieve the same output as identity seed being 1000 by executing the "DBCC CHECKIDENT ('TableName', RESEED, NewSeedValue)" script from the Up method of migration script.

However, the table definition in SQL Server still shows "IDENTITY(1,1)" even though the first record entered in the table gets an id 1000. This means that the the identity seed of the table has actually not been set to 1000. It's just that the initial 999 values are consumed or skipped while creating the first record, of course because you are firing the RESEED command to the DB i.e. you are changing the SEED that has already been set.

To conclude, it looks like, in EF core v2.1, Microsoft has not provided the provision of customizing the identity seed value while creating the table. Let us hope that the future versions of EF Core, probably 2.2 or 3.0 will have this feature. Thoughts...?

Ankur Deshmukh
  • 1
  • 1