使用C# EF Core与SQL Server插入数据时身份列报错问题

问题场景与报错 #

添加关联WelfareOffice实体的新Client时触发数据库错误：

Cannot insert explicit value for identity column in table 'WelfareOffices' when IDENTITY_INSERT is set to OFF

核心问题：将已从数据库获取的WelfareOffice实例赋值给新Client的WelfareOffice属性后，EF Core错误地尝试插入该WelfareOffice实体，而非仅建立关联关系。

环境信息：

• EF Core 7.x版本
• 未使用AsNoTracking

实体类简化代码 #

public class Client : AuditableEntity
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string FirstName { get; set; }

    [Required]
    public WelfareOffice WelfareOffice { get; set; }
}

public class WelfareOffice : AuditableEntity
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string FirstName { get; set; }

    [Required]
    public string LastName { get; set; }

    [NotMapped]
    public string FullName => FirstName + " " + LastName;
}

相关业务代码片段 #

private ClientDto _clientToAdd = new()
{
    Id = 0,
    FirstName = string.Empty,
    LastName = string.Empty,
    Birthday = default,
    Street = string.Empty,
    ZipCode = string.Empty,
    City = string.Empty,
    PhoneNumber = string.Empty,
    Email = string.Empty,
    TypeOfAid = string.Empty,
    WelfareOffice = null,
    SpecializedServiceHours = SpecializedServiceHours.Periodic,
    ApprovalPeriodFrom = default,
    ApprovalPeriodUntil = default,
    ResponsibleYouthWelfareOffice = string.Empty,
    TypeOfAppointment = string.Empty,
    Number = string.Empty,
    HoursOfSpecialistServicesInPeriod = 0,
    MonthsPerPeriod = 0
};
    
allWelfareOffices = await _httpClient.GetFromJsonAsync<IEnumerable<WelfareOfficeDto>>("api/welfareoffice/getallwelfareoffices");

_welfareOffices = _allWelfareOffices;
    
_clientToAdd.WelfareOffice = _welfareOffices.First(x => x.Id == _selectedWelfareOfficeId);

完整报错信息 #

---> Microsoft.Data.SqlClient.SqlException (0x80131904):

Cannot insert explicit value for identity column in table 'WelFareOffices' when IDENTITY_INSERT is set to OFF. Cannot insert explicit value for identity column in table 'Clients' when IDENTITY_INSERT is set to OFF.

    at Microsoft.Data.SqlClient.SqlCommand.<>c.ExecuteDbDataReaderAsync>b__209_0(Task`1 result)
    at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

解决思路 #

1. 显式使用外键属性（推荐） #

在Client实体中添加显式外键字段，直接通过外键值建立关联，避免传递整个实体：

public class Client : AuditableEntity
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string FirstName { get; set; }

    [Required]
    public int WelfareOfficeId { get; set; } // 显式外键

    [ForeignKey(nameof(WelfareOfficeId))]
    public WelfareOffice WelfareOffice { get; set; }
}

业务代码中只需赋值外键：

_clientToAdd.WelfareOfficeId = _selectedWelfareOfficeId;

2. 手动附加实体到DbContext #

如果必须传递WelfareOffice实体，需将其附加到当前DbContext并标记为已存在状态：

var selectedWelfareOffice = _welfareOffices.First(x => x.Id == _selectedWelfareOfficeId);
_context.Attach(selectedWelfareOffice).State = EntityState.Unchanged;
_clientToAdd.WelfareOffice = selectedWelfareOffice;

此操作会让EF Core识别该实体已存在于数据库，不会尝试插入新记录。

3. 检查实体状态与属性设置 #

• 确保Client的Id属性在插入时保持默认值（0），自增列无需手动赋值；
• 检查Dto转换逻辑，避免意外修改实体的跟踪状态相关属性。

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