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

[dlidstrom]

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

[Thorium]

The reference is closed, do we need changes on SQLProvider side or is all ok now?