EF Core SQL Server中DatabaseGenerated(Identity)无法自动填充DateTime列

问题根源 #

你给NotifiedAt列标记了[DatabaseGenerated(DatabaseGeneratedOption.Identity)]，但SQL Server的IDENTITY属性仅支持数值类型（如int、bigint），DateTime列无法设置为IDENTITY。这导致数据库端没有自动生成该列值的逻辑，EF生成的INSERT语句又不包含NotifiedAt（因为它认为数据库会生成），最终触发非空列插入空值的错误。

解决方案 #

方案1：用Computed特性配合数据库默认值（推荐）

1. 修改实体类中NotifiedAt的特性为[DatabaseGenerated(DatabaseGeneratedOption.Computed)]，表示该列值由数据库计算生成：

[Index(nameof(OwnerId))]
[Index(nameof(UserPrincipalName))]
[Index(nameof(NotifiedAt))]
public class ApplicationNotification
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; init; }
    [Required] public Guid OwnerId { get; set; }
    [MaxLength(256)] public string? DisplayName { get; set; }
    [MaxLength(256)] public string? GivenName { get; set; }
    [MaxLength(256)] public string? Surname { get; set; }
    [Required] [MaxLength(256)] public string? UserPrincipalName { get; set; }
    [Required] public string? Message { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime NotifiedAt { get; set; }
}

2. 生成并执行数据库迁移，给NotifiedAt列添加UTC时间默认值：
   迁移代码示例：

migrationBuilder.AlterColumn<DateTime>(
    name: "NotifiedAt",
    table: "ApplicationNotifications",
    type: "datetime2",
    nullable: false,
    defaultValueSql: "GETUTCDATE()",
    oldClrType: typeof(DateTime),
    oldType: "datetime2");

方案2：代码中手动赋值

如果不想依赖数据库默认值，直接在实体构造时初始化NotifiedAt：

[Index(nameof(OwnerId))]
[Index(nameof(UserPrincipalName))]
[Index(nameof(NotifiedAt))]
public class ApplicationNotification
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; init; }
    [Required] public Guid OwnerId { get; set; }
    [MaxLength(256)] public string? DisplayName { get; set; }
    [MaxLength(256)] public string? GivenName { get; set; }
    [MaxLength(256)] public string? Surname { get; set; }
    [Required] [MaxLength(256)] public string? UserPrincipalName { get; set; }
    [Required] public string? Message { get; set; }
    public DateTime NotifiedAt { get; set; }

    public ApplicationNotification()
    {
        NotifiedAt = DateTime.UtcNow;
    }
}

这样EF会在插入时自动将NotifiedAt的值包含在INSERT语句中，无需数据库处理。

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