Npgsql 10.0 compatibility: DATE columns throw InvalidCastException due to DateOnly default mapping change

首先使用 PostgreSQL 15.12、Npgsql 10.0.1 和 SQLProvider.PostgreSql 1.5.17，从 issue 中的 F# 复现开始。跟踪 PostgreSQL provider 在读取 date 列时调用 NpgsqlDataReader.GetDateTime() 的路径。完成标准是：在 Npgsql 10.0 下，查询能够读取 date 值而不会出现 InvalidCastException，并且受影响的行为已由适当的回归测试覆盖。

Describe the bug

SQLProvider is incompatible with Npgsql 10.0, which changed the default .NET mapping for PostgreSQL date columns from System.DateTime to System.DateOnly (and time from TimeSpan to TimeOnly).

To Reproduce

CREATE TABLE test (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    d DATE NOT NULL
);
INSERT INTO test (d) VALUES ('2024-01-28');

open FSharp.Data.Sql.PostgreSql
open FSharp.Data.Sql.Common

type Db = SqlDataProvider<
  DatabaseProviderTypes.POSTGRESQL,
  "Host=localhost;Database=test;Username=postgres;Password=test">
let ctx = Db.GetDataContext()
for row in query { for r in ctx.Public.Test do select r } do
    printfn "%A" row.D   // throws InvalidCastException with Npgsql 10

Environment (please complete the following information):

• PostgreSQL v15.12

<PackageReference Include="Npgsql" Version="10.0.1" />
<PackageReference Include="SQLProvider.PostgreSql" Version="1.5.17" />

Additional context

This is from an analysis by Claude.

SQLProvider's PostgreSQL provider internally calls GetDateTime() on the NpgsqlDataReader when reading date columns. In Npgsql 10.0, this throws an InvalidCastException at runtime because the reader now returns DateOnly by default for date columns.

The build succeeds because SQLProvider's type mapping is resolved at design time independently of Npgsql's runtime behavior. The failure only surfaces at runtime when rows are actually read.

Workaround

Apply the Npgsql 10.0 legacy behavior opt-out via AppContext switch as documented in the Npgsql 10.0 release notes.

Additional References

• Npgsql tracking issue #5328