.NET 8代码中IDENTITY_INSERT设为OFF致SQL插入失败及SSMS连接问题

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'Books' when IDENTITY_INSERT is set to OFF.
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__211_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()

无法通过代码向数据库表插入数据，仅在SSMS中执行SET IDENTITY_INSERT {TABLE_NAME} ON后直接插入才可正常进行，API的POST请求无法使用，寻求无需反复开启IDENTITY_INSERT的解决方案。

1. 避免手动指定Identity列值 #

如果Books表主键是自增Identity列，插入新实体时不要手动设置主键值，EF Core会自动处理自增赋值：

// 错误：手动设置Identity列Id
var book = new Book { Id = 100, Title = "New Book" };
_context.Books.Add(book);

// 正确：不设置Id，由EF自动生成
var book = new Book { Title = "New Book" };
_context.Books.Add(book);

2. 配置EF Core实体映射（需手动插入Identity值场景） #

若业务必须手动插入Identity列值，通过Fluent API配置实体，无需每次执行SET IDENTITY_INSERT：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>()
        .Property(b => b.Id)
        .ValueGeneratedOnAdd()
        .Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Save);
}

插入时需显式标记Id为已修改：

var book = new Book { Id = 100, Title = "New Book" };
_context.Books.Add(book);
_context.Entry(book).Property(b => b.Id).IsModified = true;
await _context.SaveChangesAsync();

3. 检查上下文SaveChanges逻辑 #

排查是否有全局拦截、审计日志等逻辑无意中修改了主键值，导致EF尝试插入显式Identity值。

4. 验证数据库表结构 #

确认Books表主键的IDENTITY属性未被误修改，执行以下SQL检查：

SELECT 
    COLUMN_NAME, 
    IS_IDENTITY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Books' AND COLUMN_NAME = 'Id';

内容的提问来源于stack exchange，提问作者Kunal Sisodia