使用Entity Framework插入数据失败，报错无法插入标识列值

实体类定义 #

[Table("ServiceTickets", Schema = "dbo")]
public class ServiceTicket
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public DateTime CreationDateTime { get; set; } = DateTime.Now;
    [Column(TypeName = "varchar(1000)")]
    public string Issue { get; set; } = string.Empty;
    public DateTime? ReportedDateTime { get; set; }
    public DateTime? ResolutionDateTime { get; set; }
    public int CreatedBy { get; set; }
    public int AttendedBy { get; set; }
    public int ConfirmedBy { get; set; }
    public int SrNumber { get; set; }
    public int StatusId { get; set; }

    public ServiceTicketSubInformation? ServiceTicketSubInformation { get; set; }
    public virtual StatusEnum? StatusEnums { get; set; }
    public virtual ICollection<WorkOrder>? WorkOrders { get; set; }
}

[Table("ServiceTicketSubInfo", Schema = "dbo")]
public class ServiceTicketSubInformation
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int LocationId { get; set; }
    public int SubLocationId { get; set; }
    public int ReporterId { get; set; }
}

[Table("WorkOrder", Schema = "dbo")]
public class WorkOrder
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public DateTime? CreationDateTime { get; set; }
    [Column(TypeName = "varchar(1000)")]
    public string ActionTaken { get; set; } = string.Empty;
    public DateTime? WorkStartTime { get; set; }
    public DateTime? WorkCompleteTime { get; set; }
    public int SrNumber { get; set; }
    public int Status { get; set; }
}

[Table("Statuses", Schema = "dbo")]
public class StatusEnum 
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Column(TypeName = "varchar(50)")]
    public string Name { get; set; } = string.Empty;
}

传入的JSON数据 #

{
  "id": 0,
  "creationDateTime": "2022-07-24T12:41:49.666Z",
  "issue": "My Issue",
  "reportedDateTime": "2022-07-24T12:41:49.666Z",
  "resolutionDateTime": "2022-07-24T12:41:49.666Z",
  "createdBy": 1,
  "attendedBy": 1,
  "confirmedBy": 1,
  "srNumber": 1,
  "statusId": 1,
  "serviceTicketSubInformation": {
    "id": 1,
    "locationId": 1,
    "subLocationId": 1,
    "reporterId": 1
  },
  "statusEnums": {
    "id": 1,
    "name": "Completed"
  },
  "workOrders": [
    {
      "id": 1,
      "creationDateTime": "2022-07-24T12:41:49.666Z",
      "actionTaken": "My Action Taken",
      "workStartTime": "2022-07-24T12:41:49.666Z",
      "workCompleteTime": "2022-07-24T12:41:49.666Z",
      "srNumber": 1,
      "status": 1
    }
  ]
}

插入操作代码 #

public async Task<ServiceTicket> UpsertServiceTicket(ServiceTicket model)
{
    await context.ServiceTickets.AddAsync(model);
    var result = await context.SaveChangesAsync();
    model.Id = result;
    return model;
}

报错信息 #

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 'ServiceTicketSubInfo' when IDENTITY_INSERT is set to OFF.

at Microsoft.Data.SqlClient.SqlCommand.<>c.b__188_0(Task`1 result)

错误原因 #

• ServiceTicketSubInformation、StatusEnum、WorkOrder的Id字段都标记了[DatabaseGenerated(DatabaseGeneratedOption.Identity)]，属于SQL Server的自增身份列，默认不允许手动插入显式值。但你传入的JSON里给这些子实体都指定了id值（比如serviceTicketSubInformation的id:1），EF Core会尝试把这些值写入数据库，触发了SQL的限制。
• 另外插入代码里的model.Id = result;逻辑错误：SaveChangesAsync()返回的是受影响的行数，不是新生成的主键值，EF Core会自动把数据库生成的主键赋值给实体的Id属性。

解决办法 #

1. 修改传入的JSON：移除所有自增实体的id字段，让数据库自动生成主键：

{
  "id": 0,
  "creationDateTime": "2022-07-24T12:41:49.666Z",
  "issue": "My Issue",
  "reportedDateTime": "2022-07-24T12:41:49.666Z",
  "resolutionDateTime": "2022-07-24T12:41:49.666Z",
  "createdBy": 1,
  "attendedBy": 1,
  "confirmedBy": 1,
  "srNumber": 1,
  "statusId": 1,
  "serviceTicketSubInformation": {
    "locationId": 1,
    "subLocationId": 1,
    "reporterId": 1
  },
  "workOrders": [
    {
      "creationDateTime": "2022-07-24T12:41:49.666Z",
      "actionTaken": "My Action Taken",
      "workStartTime": "2022-07-24T12:41:49.666Z",
      "workCompleteTime": "2022-07-24T12:41:49.666Z",
      "srNumber": 1,
      "status": 1
    }
  ]
}

注：statusEnums字段可以直接删除，因为你已经通过StatusId=1关联了已存在的状态记录，保留该字段会导致EF Core尝试插入重复的状态数据。

2. 修正插入代码：去掉错误的主键赋值逻辑：

public async Task<ServiceTicket> UpsertServiceTicket(ServiceTicket model)
{
    await context.ServiceTickets.AddAsync(model);
    await context.SaveChangesAsync();
    // EF Core已自动将生成的Id赋值给model.Id，无需手动设置
    return model;
}

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