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

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

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

I have implemented the Npgsql workaround they have documented. Here's the class I'm using:

// Copied from https://www.npgsql.org/doc/release-notes/10.0.html
// Required until FSharp.Data.SqlProvider adds DateOnly/TimeOnly support for Npgsql 10+.
// See: https://github.com/npgsql/doc/blob/main/static/LegacyDateAndTimeResolverFactory.cs

using Npgsql.Internal;
using Npgsql.Internal.Postgres;
using NpgsqlTypes;

namespace NpgsqlCompat;

#pragma warning disable NPG9001 // Type is for evaluation purposes only and is subject to change or removal in future updates. Suppress this diagnostic to proceed.
public sealed class LegacyDateAndTimeResolverFactory : PgTypeInfoResolverFactory
{
  public override IPgTypeInfoResolver CreateResolver()
  {
    return new Resolver();
  }

  public override IPgTypeInfoResolver CreateArrayResolver()
  {
    return new ArrayResolver();
  }

  public override IPgTypeInfoResolver CreateRangeResolver()
  {
    return new RangeResolver();
  }

  public override IPgTypeInfoResolver CreateRangeArrayResolver()
  {
    return new RangeArrayResolver();
  }

  public override IPgTypeInfoResolver CreateMultirangeResolver()
  {
    return new MultirangeResolver();
  }

  public override IPgTypeInfoResolver CreateMultirangeArrayResolver()
  {
    return new MultirangeArrayResolver();
  }

  private const string Date = "pg_catalog.date";
  private const string Time = "pg_catalog.time";
  private const string DateRange = "pg_catalog.daterange";
  private const string DateMultirange = "pg_catalog.datemultirange";

  private class Resolver : IPgTypeInfoResolver
  {
    protected TypeInfoMappingCollection Mappings => field ??= AddMappings(new());

    public PgTypeInfo? GetTypeInfo(Type? type, DataTypeName? dataTypeName, PgSerializerOptions options)
    {
      return type == typeof(object) ? Mappings.Find(type, dataTypeName, options) : null;
    }

    private static TypeInfoMappingCollection AddMappings(TypeInfoMappingCollection mappings)
    {
      mappings.AddStructType<DateTime>(Date,
          static (options, mapping, _) => options.GetTypeInfo(typeof(DateTime), new DataTypeName(mapping.DataTypeName))!,
          matchRequirement: MatchRequirement.DataTypeName);

      mappings.AddStructType<TimeSpan>(Time,
          static (options, mapping, _) => options.GetTypeInfo(typeof(TimeSpan), new DataTypeName(mapping.DataTypeName))!,
          isDefault: true);

      return mappings;
    }
  }

  private sealed class ArrayResolver : Resolver, IPgTypeInfoResolver
  {
    private new TypeInfoMappingCollection Mappings => field ??= AddMappings(new(base.Mappings));

    public new PgTypeInfo? GetTypeInfo(Type? type, DataTypeName? dataTypeName, PgSerializerOptions options)
    {
      return type == typeof(object) ? Mappings.Find(type, dataTypeName, options) : null;
    }

    private static TypeInfoMappingCollection AddMappings(TypeInfoMappingCollection mappings)
    {
      mappings.AddStructArrayType<DateTime>(Date);
      mappings.AddStructArrayType<TimeSpan>(Time);

      return mappings;
    }
  }

  private class RangeResolver : IPgTypeInfoResolver
  {
    protected TypeInfoMappingCollection Mappings => field ??= AddMappings(new());

    public PgTypeInfo? GetTypeInfo(Type? type, DataTypeName? dataTypeName, PgSerializerOptions options)
    {
      return type == typeof(object) ? Mappings.Find(type, dataTypeName, options) : null;
    }

    private static TypeInfoMappingCollection AddMappings(TypeInfoMappingCollection mappings)
    {
      mappings.AddStructType<NpgsqlRange<DateTime>>(DateRange,
          static (options, mapping, _) => options.GetTypeInfo(typeof(NpgsqlRange<DateTime>), new DataTypeName(mapping.DataTypeName))!,
          matchRequirement: MatchRequirement.DataTypeName);

      return mappings;
    }
  }

  private sealed class RangeArrayResolver : RangeResolver, IPgTypeInfoResolver
  {
    private new TypeInfoMappingCollection Mappings => field ??= AddMappings(new(base.Mappings));

    public new PgTypeInfo? GetTypeInfo(Type? type, DataTypeName? dataTypeName, PgSerializerOptions options)
    {
      return type == typeof(object) ? Mappings.Find(type, dataTypeName, options) : null;
    }

    private static TypeInfoMappingCollection AddMappings(TypeInfoMappingCollection mappings)
    {
      mappings.AddStructArrayType<NpgsqlRange<DateTime>>(DateRange);

      return mappings;
    }
  }

  private class MultirangeResolver : IPgTypeInfoResolver
  {
    protected TypeInfoMappingCollection Mappings => field ??= AddMappings(new());

    public PgTypeInfo? GetTypeInfo(Type? type, DataTypeName? dataTypeName, PgSerializerOptions options)
    {
      return type == typeof(object) ? Mappings.Find(type, dataTypeName, options) : null;
    }

    private static TypeInfoMappingCollection AddMappings(TypeInfoMappingCollection mappings)
    {
      mappings.AddType<NpgsqlRange<DateTime>[]>(DateMultirange,
          static (options, mapping, _) => options.GetTypeInfo(typeof(NpgsqlRange<DateTime>[]), new DataTypeName(mapping.DataTypeName))!,
          matchRequirement: MatchRequirement.DataTypeName);

      return mappings;
    }
  }

  private sealed class MultirangeArrayResolver : MultirangeResolver, IPgTypeInfoResolver
  {
    private new TypeInfoMappingCollection Mappings => field ??= AddMappings(new(base.Mappings));

    public new PgTypeInfo? GetTypeInfo(Type? type, DataTypeName? dataTypeName, PgSerializerOptions options)
    {
      return type == typeof(object) ? Mappings.Find(type, dataTypeName, options) : null;
    }

    private static TypeInfoMappingCollection AddMappings(TypeInfoMappingCollection mappings)
    {
      mappings.AddArrayType<NpgsqlRange<DateTime>[]>(DateMultirange);

      return mappings;
    }
  }
}

I register this class at startup:

[<EntryPoint>]
let main argv =
  // SQLProvider does not yet support Npgsql 10's DateOnly/TimeOnly mapping;
  // register the legacy resolver so date/time columns materialise as DateTime/TimeSpan.
  Npgsql.NpgsqlConnection.GlobalTypeMapper.AddTypeInfoResolverFactory(
    NpgsqlCompat.LegacyDateAndTimeResolverFactory())

Navigation Menu

Repository navigation

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

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

Activity

Thorium commented on Jun 12, 2026

dlidstrom commented on Aug 26, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions