已开启IDENTITY_INSERT仍报错？EF Core插入OrderItems遇SQL异常

实体类代码 #

public class OrderItem
{
    [Key]
    public int Id { get; set; }
    public int Amount { get; set; }
    public double Price { get; set; }

    [ForeignKey("MovieId")]
    public int MovieId { get; set; }
    public Movie? Movie { get; set; }
}

迁移代码 #

migrationBuilder.CreateTable(
                name: "OrderItems",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Amount = table.Column<int>(type: "int", nullable: false),
                    Price = table.Column<double>(type: "float", nullable: false),
                    MovieId = table.Column<int>(type: "int", nullable: false),
                    OrderId = table.Column<int>(type: "int", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_OrderItems", x => x.Id);
                    table.ForeignKey(
                        name: "FK_OrderItems_Movies_MovieId",
                        column: x => x.MovieId,
                        principalTable: "Movies",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_OrderItems_Orders_OrderId",
                        column: x => x.OrderId,
                        principalTable: "Orders",
                        principalColumn: "Id");
                });

报错信息 #

SqlException: Cannot insert explicit value for identity column in table 'OrderItems' when IDENTITY_INSERT is set to OFF.

在SQL Server Management Studio中操作完全正常，尝试以下代码后问题依旧：

await _context.Database.ExecuteSqlRawAsync("SET IDENTITY_INSERT OrderItems ON");
await _context.OrderItems.AddAsync(orderItem); 

问题原因 #

1. 手动赋值Id属性：即使数据库中Id是标识列，如果你显式给orderItem.Id赋值，EF Core会尝试将该值插入数据库，触发SQL Server的身份列限制。
2. 会话不共享：SET IDENTITY_INSERT是会话级命令，ExecuteSqlRawAsync会开启独立会话执行命令，执行完毕后会话关闭，后续AddAsync使用的是新会话，之前的设置不会生效。

解决办法 #

方法1：不手动赋值Id（推荐） #

如果不需要指定Id，直接跳过对Id属性的赋值，EF Core会自动让数据库生成标识值：

var orderItem = new OrderItem
{
    Amount = 1,
    Price = 9.99,
    MovieId = 1
    // 不设置Id属性
};
await _context.OrderItems.AddAsync(orderItem);
await _context.SaveChangesAsync();

方法2：正确开启IDENTITY_INSERT（需手动指定Id时） #

要在同一个数据库会话中完成开启命令、插入操作、关闭命令：

await using var transaction = await _context.Database.BeginTransactionAsync();
try
{
    await _context.Database.ExecuteSqlRawAsync("SET IDENTITY_INSERT OrderItems ON");
    
    var orderItem = new OrderItem
    {
        Id = 100, // 手动指定Id
        Amount = 1,
        Price = 9.99,
        MovieId = 1
    };
    _context.OrderItems.Add(orderItem);
    await _context.SaveChangesAsync();
    
    await _context.Database.ExecuteSqlRawAsync("SET IDENTITY_INSERT OrderItems OFF");
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

同时需要配置EF Core允许手动赋值标识列，二选一即可：

• 实体类注解：

  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int Id { get; set; }
  

• Fluent API配置（在DbContext的OnModelCreating方法中）：

  modelBuilder.Entity<OrderItem>()
      .Property(o => o.Id)
      .ValueGeneratedNever();
  

内容的提问来源于stack exchange，提问作者Tudor G