当 IDENTITY_INSERT 设置为 OFF 时，无法为标识列插入显式值。（实体框架核心）

Question

当我尝试将新实体添加到数据库中时，出现此错误。实体名称为DestuffedContainer. 该实体及相关实体的定义如下：

去填充容器：

[Table("DestuffedContainer")]

public class DestuffedContainer

{

    public long DestuffedContainerId { get; set; }

    public int Index { get; set; }

    public string Description { get; set; }

    public int? PackageQuantity { get; set; }

    public string PackageType { get; set; }

    public double? CBM { get; set; }

    public string Location { get; set; }

    public string MarksAndNumber { get; set; }

    public int? ManifestWeight { get; set; }

    public int? FoundWeight { get; set; }

    public int? ManifestQuantity { get; set; }

    public string ConsigneeName { get; set; }

    public string Remarks { get; set; }

    public string InvoiceFound { get; set; }

    public string PackageFound { get; set; }

    public long TellySheetId { get; set; }

    public TellySheet TellySheet { get; set; }

    public long ContainerIndexId { get; set; }

    public ContainerIndex ContainerIndex { get; set; }

}

电视表：

[Table("TellySheet")]

public class TellySheet

{

    public TellySheet()

    {

        DestuffedContainers = new List<DestuffedContainer>();

    }

    public long TellySheetId { get; set; }

    public string TellyClerk { get; set; }

    public DateTime? DestuffDate { get; set; }

    public string DayNight { get; set; }

    public long ShippingAgentId { get; set; }

    public ShippingAgent ShippingAgent { get; set; }

    public List<DestuffedContainer> DestuffedContainers { get; set; }

}

我不确定这个错误意味着什么，因为我没有明确指定 destuffedcontainer 实体的主键值，默认情况下它是 0。实体框架应该将其作为插入，但它会抛出错误。

几天前它工作正常，但我不确定自从导致此错误以来发生了什么变化。

我正在使用 Entity Framework Core 来对我的实体进行建模。我尝试了几种解决方案，但似乎都不起作用。如果您能帮助解决此问题，我将不胜感激。

Answer 1

该错误表明您无法在 IDENTITY 列上插入数据，因为这样做已被禁用。您可以通过执行以下操作来启用此行为SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }当您使用实体框架核心时，您必须：context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.DestuffedContainer ON");context.SaveChanges();context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.DestuffedContainer OFF");> SQL Server IDENTITY 列中的显式值编辑我很抱歉我无法理解您没有提供价值。然后确保您的列被标记为模型的 IDENTITY。我看到您正在使用属性来告诉 efcore 如何命名您的表。只要您不使用Id关键属性的常规名称，您就需要在实体类上使用[Key]属性来明确它，在属性之上，例如：[Key]public long DestuffedContainerId { get; set; }或者在上下文类中使用 FluentAPI：protected override void OnModelCreating(ModelBuilder modelBuilder){ ...    modelBuilder    .Entity<DestuffedContainer>()    .Property(d => d.DestuffedContainerId)    .ValueGeneratedOnAdd()     modelBuilder    .Entity<DestuffedContainer>()    .HasKey(d => d.DestuffedContainerId) ...}还要检查 sql server 表定义并确保以您的属性命名的字段设置为IDENTITY，考虑到您收到的错误，这是肯定的，但值得检查。

Answer 2

对于未来的读者。我遇到了相反的问题。我不希望 IDENTITY 自动开启。所以像这样：（非常简单的流畅映射）        builder.HasKey(ent => ent.MyColumnWhichIsTheKey);我必须添加这一行（ValueGenerateNever）以保持 IDENITY 处于关闭状态（故意“关闭”）：        builder.HasKey(ent => ent.MyColumnWhichIsTheKey);         builder.Property(ent => ent.MyColumnWhichIsTheKey).ValueGeneratedNever();其他提示：using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Metadata.Builders;和<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.3" /> <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.3" />

Answer 3

以防万一有人在 2021 年使用 EF Core 5.x 发现这个问题，我为此苦苦挣扎了几个小时，经过大量搜索后没有找到我的解决方案。就我而言，我有一个现有数据库，并使用 EF Core 脚手架命令为我创建数据库上下文和关联的模型类。当尝试将新的 TimeEntry 插入数据库中的 TimeEntry 表时，如下所示：TimeEntry timeEntry = new TimeEntry&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EmployeeId = Emp.Id,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ScheduleTypeId = SchedTypeId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FacilityId = FacilityId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeIn1 = DateTimeIn1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeIn2 = DateTimeIn2,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeIn3 = DateTimeIn3,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeIn4 = DateTimeIn4,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeIn5 = DateTimeIn5,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeIn6 = DateTimeIn6,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeIn7 = DateTimeIn7,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeOut1 = DateTimeOut1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeOut2 = DateTimeOut2,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeOut3 = DateTimeOut3,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeOut4 = DateTimeOut4,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeOut5 = DateTimeOut5,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeOut6 = DateTimeOut6,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTimeOut7 = DateTimeOut7,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Day1TotalHours = day1TotalHoursDecimal,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Day2TotalHours = day2TotalHoursDecimal,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Day3TotalHours = day3TotalHoursDecimal,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Day4TotalHours = day4TotalHoursDecimal,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Day5TotalHours = day5TotalHoursDecimal,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Day6TotalHours = day6TotalHoursDecimal,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Day7TotalHours = day7TotalHoursDecimal,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WeekStartDate = StartDateForWeek,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WeekTotalHours = WeekTotalHoursDecimal&nbsp; &nbsp; &nbsp; &nbsp; };&nbsp; &nbsp; &nbsp; &nbsp; db.TimeEntries.Add(timeEntry);&nbsp; &nbsp; &nbsp; &nbsp; db.SaveChanges();尽管我没有明确尝试插入 ID，但我还是收到了此错误。经过多次尝试，我终于找到了解决方法，尝试了我能找到的每一篇文章中的所有内容，但没有任何效果。此代码解决了我的 DbContext 类中 id 字段的问题：modelBuilder.Entity<TimeEntry>(entity =>&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; entity.ToTable("TimeEntry");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; entity.Property(e => e.Id).HasColumnName("id").ValueGeneratedOnAdd(); // <-- This fixed it for me另请注意，在其他帖子之后，我也在我的 TimeEntry 模型类中进行了此设置，不确定是否重要以避免此错误：public partial class TimeEntry{&nbsp; &nbsp; [DatabaseGenerated(DatabaseGeneratedOption.Identity)]&nbsp; &nbsp; [Key]&nbsp; &nbsp; public int Id { get; set; } // rest of code removed for brevity我真的希望这对未来的人有所帮助，这让我发疯。

Answer 4

有时，当开发人员在 Context 中手动创建 modelBuilder 时，他/她可能会编写这样的代码，并且准确地 .ValueGenerateNever() -这部分返回您主题中的错误entity.Property(e&nbsp;=>&nbsp;e.Id).ValueGeneratedNever();

Answer 5

如果您想保持 Identity_Insert 关闭并允许在定义的应用程序上下文上的 OnModelCreating 方法中手动插入，则这是一个比所选答案更好的解决方案这是使用 EF Core for NET 5.0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;modelBuilder.Entity<EntityType>().HasKey(x&nbsp;=>&nbsp;x.KeyWithIdentityValue); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;modelBuilder.Entity<EntityType>().Property(x&nbsp;=>&nbsp;x.KeyWithIdentityValue).ValueGeneratedOnAdd();```