.NET6+EF Core+PostgreSQL：Local DateTime写入无时区字段报错

使用以下环境的.NET 6控制台应用出现DateTime写入异常：

• NuGet包：
  • Microsoft.EntityFrameworkCore, Version 6.0.8
  • Npgsql.EntityFrameworkCore.PostgreSQL, Version 6.0.6
  • Npgsql, Version 6.0.6
• PostgreSQL版本：14.4

通过SQL脚本创建表：

create table if not exists "Messages" (
    "Id" uuid primary key,
    "Time" timestamp without time zone,
    "MessageType" text,
    "Message" text
);

EF Core上下文及实体类代码：

namespace Database;

public class MessageContext : DbContext
{
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    var dbConfig = Configurer.Config.MessageDbConfig;
    optionsBuilder.UseNpgsql(new NpgsqlConnectionStringBuilder
      {
        Database = dbConfig.DbName,
        Username = dbConfig.Username,
        Password = dbConfig.Password,
        Host = dbConfig.Host,
        Port = dbConfig.Port,
        Pooling = dbConfig.Pooling
      }.ConnectionString,
     optionsBuilder => optionsBuilder.SetPostgresVersion(Version.Parse("14.4")));

    base.OnConfiguring(optionsBuilder);
  }

  public DbSet<Msg> Messages { get; set; }
}

public class Msg
{
  public Guid Id { get; set; } = Guid.NewGuid();
  public DateTime Time { get; set; } = DateTime.Now;
  public string MessageType { get; set; }
  public string Message { get; set; }
}

执行以下代码保存实体时抛出异常：

using var context = new MessageContext();
context.Messages.Add(new Msg
{
  MessageType = message.GetType()
    .FullName,
  Message = message.ToString()
});
context.SaveChanges();

异常信息：

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> System.InvalidCastException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
   at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)

核心矛盾：数据库字段是timestamp without time zone，但报错提示尝试写入timestamp with time zone类型，使用DateTime.UtcNow则正常。

这是Npgsql与EF Core的默认DateTime映射规则导致的：

1. 默认映射逻辑：Npgsql提供程序在未显式配置属性映射时，会根据.NET DateTime的Kind属性推断对应的PostgreSQL类型：
   • 当DateTime.Kind为Local时，默认映射为PostgreSQL的timestamp with time zone（timestamptz）
   • 当DateTime.Kind为Utc时，默认映射逻辑会适配timestamp without time zone字段，因此可以正常写入
2. 属性未显式配置：你的实体类Msg的Time属性没有配置对应数据库字段的类型，EF Core会按照默认规则处理参数类型。由于DateTime.Now生成的Kind是Local，Npgsql会尝试以timestamptz类型写入，与数据库实际的timestamp without time zone字段类型不匹配，触发异常。

有三种可行的解决方式：

1. 显式配置字段映射：在MessageContext的OnModelCreating方法中，指定Time属性对应数据库的timestamp without time zone类型：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Msg>()
        .Property(m => m.Time)
        .HasColumnType("timestamp without time zone");
}

2. 使用UTC时间赋值：将实体Time属性的默认值改为DateTime.UtcNow，确保Kind为Utc，匹配数据库字段类型：

public DateTime Time { get; set; } = DateTime.UtcNow;

3. 启用旧版时间戳兼容（不推荐）：通过AppContext开关启用Npgsql的旧版时间戳处理逻辑，忽略DateTime.Kind的差异，仅作为临时兼容方案：

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

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