ASP.NET Code-First项目执行update-database遇IDENTITY_INSERT报错的解决方法

Ah, this classic EF Code-First gotcha! I’ve run into this a few times myself when setting up migrations and seed data. Let’s break down the common fixes depending on what’s causing the error.

Cause 1: You’re seeding data with explicit IDs for an identity column #

By default, EF Code-First marks your primary key (usually Id) as an identity column—meaning SQL Server generates the value automatically. If your Seed method is setting the Id property explicitly, SQL will throw this error because it expects to handle ID generation itself.

Fixes: #

• Option 1: Omit the ID from seed entities: Let SQL handle generating IDs automatically. This is the simplest approach if you don’t need fixed ID values:

  protected override void Seed(YourDbContext context)
  {
      context.Movies.AddOrUpdate(
          m => m.Title, // Use a unique property to avoid duplicates
          new Movie { Title = "Inception", ReleaseYear = 2010 },
          new Movie { Title = "The Dark Knight", ReleaseYear = 2008 }
      );
      context.SaveChanges();
  }
  

• Option 2: Enable IDENTITY_INSERT temporarily: If you need fixed IDs (e.g., for reference data that relies on specific values), toggle the setting before inserting:

  protected override void Seed(YourDbContext context)
  {
      // Turn on IDENTITY_INSERT for the Movies table
      context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Movies ON");
  
      context.Movies.AddOrUpdate(
          m => m.Id,
          new Movie { Id = 1, Title = "Inception", ReleaseYear = 2010 },
          new Movie { Id = 2, Title = "The Dark Knight", ReleaseYear = 2008 }
      );
  
      context.SaveChanges();
  
      // Turn off IDENTITY_INSERT to restore default behavior
      context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Movies OFF");
  }
  

  Use AddOrUpdate instead of Add to avoid duplicate entries if you run update-database multiple times.

Cause 2: Your migration includes InsertData with explicit IDs #

If your generated migration uses InsertData and specifies the Id column, SQL will reject the insert unless IDENTITY_INSERT is enabled. For example:

migrationBuilder.InsertData(
    table: "Movies",
    columns: new[] { "Id", "Title", "ReleaseYear" },
    values: new object[] { 1, "Inception", 2010 });

Fix: #

Modify the migration to either:

• Remove the Id column: Let SQL generate the ID automatically:

  migrationBuilder.InsertData(
      table: "Movies",
      columns: new[] { "Title", "ReleaseYear" },
      values: new object[] { "Inception", 2010 });
  

• Wrap inserts in SQL commands: Replace InsertData with raw SQL to toggle IDENTITY_INSERT:

  migrationBuilder.Sql("SET IDENTITY_INSERT Movies ON;");
  migrationBuilder.Sql("INSERT INTO Movies (Id, Title, ReleaseYear) VALUES (1, 'Inception', 2010);");
  migrationBuilder.Sql("SET IDENTITY_INSERT Movies OFF;");
  

Cause 3: Conflicting entity configuration #

Double-check that your Movie entity’s Id property is correctly configured as an identity column. The default convention works for properties named Id or MovieId, but if you’ve overridden this, it can cause conflicts.

Verify configuration: #

• Using data annotations:

  public class Movie
  {
      [Key]
      [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      public int Id { get; set; }
      // Other properties...
  }
  

• Using fluent API in your DbContext:

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<Movie>()
          .Property(m => m.Id)
          .ValueGeneratedOnAdd(); // Equivalent to Identity specification
  }
  

If you accidentally set DatabaseGeneratedOption.None, EF will try to insert explicit IDs, which clashes with SQL’s identity column setting.

Final Troubleshooting Step #

If none of the above works, check your SQL Server table directly to confirm the Id column has Identity Specification set to Yes (Is Identity = Yes). Sometimes migrations don’t apply correctly, so verifying the schema manually can catch hidden issues.

内容的提问来源于stack exchange，提问作者Nehemiah Limo