Support NodaTime.Interval when using WithoutOverlaps()

[danbluhmhansen]

Hello there.

I am trying out the version 11 preview to get the .WithoutOverlaps() extension for temporal keys, along with the NodaTime types. It seems it is not possible to use the Interval NodaTime type to configure WITHOUT OVERLAPS. I get the following error when generating a migration:

Unable to create a 'DbContext' of type 'TimeDbContext'. The exception 'Property 'TimeEntry.Period' cannot be used as a key because it has type 'Interval' which does not implement 'IComparable<T>', 'IComparable' or 'IStructuralComparable'. Use 'HasConversion' in 'OnModelCreating' to wrap 'Interval' with a type that can be compared.' was thrown while attempting to create an instance. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

I have made the following overrides to get it working again:

Overrides

Override these services in the DbContext:

    protected override void OnConfiguring(DbContextOptionsBuilder builder) =>
        builder
            .UseNpgsql(
                _connectionString,
                options => options.SetPostgresVersion(18, 3).UseNodaTime()
            )
            .ReplaceService<IConventionSetBuilder, CustomNpgsqlConventionSetBuilder>()
            .ReplaceService<IModelValidator, CustomNpgsqlModelValidator>();

With the following:

public sealed class CustomNpgsqlConventionSetBuilder(
    ProviderConventionSetBuilderDependencies dependencies,
    RelationalConventionSetBuilderDependencies relationalDependencies,
    IRelationalTypeMappingSource typeMappingSource,
    INpgsqlSingletonOptions npgsqlSingletonOptions
)
    : NpgsqlConventionSetBuilder(
        dependencies,
        relationalDependencies,
        typeMappingSource,
        npgsqlSingletonOptions
    ),
        IConventionSetBuilder
{
    private readonly NpgsqlTypeMappingSource _typeMappingSource =
        (NpgsqlTypeMappingSource)typeMappingSource;
    private readonly IReadOnlyList<EnumDefinition> _enumDefinitions =
        npgsqlSingletonOptions.EnumDefinitions;

    public override ConventionSet CreateConventionSet()
    {
        var set = base.CreateConventionSet();

        var existingFinalizingConvention = set
            .ModelFinalizingConventions.OfType<NpgsqlPostgresModelFinalizingConvention>()
            .SingleOrDefault();

        if (existingFinalizingConvention is not null)
        {
            set.ModelFinalizingConventions.Remove(existingFinalizingConvention);
        }

        set.ModelFinalizingConventions.Add(
            new CustomNpgsqlPostgresModelFinalizingConvention(_typeMappingSource, _enumDefinitions)
        );

        var existingRuntimeModelConvention = set
            .ModelFinalizedConventions.OfType<NpgsqlRuntimeModelConvention>()
            .SingleOrDefault();

        if (existingRuntimeModelConvention is not null)
            set.ModelFinalizedConventions.Remove(existingRuntimeModelConvention);

        set.ModelFinalizedConventions.Add(
            new CustomNpgsqlRuntimeModelConvention(Dependencies, RelationalDependencies)
        );

        return set;
    }
}

/// <inheritdoc />
public sealed class CustomNpgsqlRuntimeModelConvention(
    ProviderConventionSetBuilderDependencies dependencies,
    RelationalConventionSetBuilderDependencies relationalDependencies
) : NpgsqlRuntimeModelConvention(dependencies, relationalDependencies)
{
    /// <inheritdoc />
    protected override void ProcessPropertyAnnotations(
        Dictionary<string, object?> annotations,
        IProperty property,
        RuntimeProperty runtimeProperty,
        bool runtime
    )
    {
        base.ProcessPropertyAnnotations(annotations, property, runtimeProperty, runtime);

        if (
            (property.IsKey() || property.IsForeignKey())
            && property.FindTypeMapping() is IntervalRangeMapping
        )
        {
            runtimeProperty.SetCurrentValueComparer(
                new EntryCurrentValueComparer(runtimeProperty, new IntervalCurrentValueComparer())
            );
        }
    }
}

/// <inheritdoc />
public sealed class CustomNpgsqlPostgresModelFinalizingConvention(
    NpgsqlTypeMappingSource typeMappingSource,
    IReadOnlyList<EnumDefinition> enumDefinitions
) : NpgsqlPostgresModelFinalizingConvention(typeMappingSource, enumDefinitions)
{
    /// <inheritdoc />
    protected override void SetRangeCurrentValueComparer(
        IConventionProperty property,
        RelationalTypeMapping typeMapping
    )
    {
        base.SetRangeCurrentValueComparer(property, typeMapping);

        if (
            (property.IsKey() || property.IsForeignKey())
            && typeMapping is IntervalRangeMapping
            && property is PropertyBase propertyBase
        )
        {
            propertyBase.SetCurrentValueComparer(
                new EntryCurrentValueComparer(
                    (IProperty)property,
                    new IntervalCurrentValueComparer()
                )
            );
        }
    }
}

/// <summary>
/// Current-value comparer for NodaTime.Interval with range-like semantics.
/// Designed to be used as EF Core ValueComparer for mapped Interval properties.
/// </summary>
public sealed class IntervalCurrentValueComparer
    : ValueComparer<Interval>,
        IComparer,
        IComparer<Interval>
{
    public static readonly IntervalCurrentValueComparer Instance = new();

    public IntervalCurrentValueComparer()
        : base((x, y) => EqualsCore(x, y), v => GetHashCodeCore(v), v => v) { }

    private static bool EqualsCore(Interval x, Interval y)
    {
        // Interval is a value type with value-based equality in NodaTime,
        // but we make intent explicit and robust here.
        return x.Start == y.Start && x.End == y.End;
    }

    private static int GetHashCodeCore(Interval v)
    {
        unchecked
        {
            var h = 17;
            h = (h * 31) + v.Start.GetHashCode();
            h = (h * 31) + v.End.GetHashCode();
            return h;
        }
    }

    public override Interval Snapshot(Interval v)
        // Interval is immutable, so direct return is a valid snapshot.
        =>
        v;

    /// <summary>
    /// Optional ordering helper equivalent to range ordering intent:
    /// start first, then end.
    /// </summary>
    public int Compare(Interval x, Interval y)
    {
        var c = x.Start.CompareTo(y.Start);
        return c != 0 ? c : x.End.CompareTo(y.End);
    }

    /// <summary>
    /// Non-generic comparer implementation.
    /// </summary>
    int IComparer.Compare(object? x, object? y)
    {
        return ReferenceEquals(x, y) ? 0
            : x is null ? -1
            : y is null ? 1
            : x is Interval ix && y is Interval iy ? Compare(ix, iy)
            : throw new ArgumentException(
                $"Both arguments must be of type {typeof(Interval).FullName}."
            );
    }
}

public sealed class CustomNpgsqlModelValidator(
    ModelValidatorDependencies dependencies,
    RelationalModelValidatorDependencies relationalDependencies,
    INpgsqlSingletonOptions npgsqlSingletonOptions
) : NpgsqlModelValidator(dependencies, relationalDependencies, npgsqlSingletonOptions)
{
    private readonly Version _postgresVersion = npgsqlSingletonOptions.PostgresVersion;

    protected override void ValidateKey(
        IKey key,
        IDiagnosticsLogger<DbLoggerCategory.Model.Validation> logger
    )
    {
        // Keep all default validations first
        ValidateShadowKey(key, logger);
        ValidateMutableKey(key, logger);
        ValidateDefaultValuesOnKey(key, logger);
        ValidateValueGeneration(key, logger);

        if (key.GetWithoutOverlaps() == true)
        {
            ValidateWithoutOverlapsKeyAllowingInterval(key);
        }
    }

    private void ValidateWithoutOverlapsKeyAllowingInterval(IKey key)
    {
        var keyName = key.IsPrimaryKey()
            ? "primary key"
            : $"alternate key {key.Properties.Format()}";
        var entityType = key.DeclaringEntityType;

        // Keep PG18 requirement
        if (_postgresVersion < new Version(18, 0))
        {
            throw new InvalidOperationException(
                NpgsqlStrings.WithoutOverlapsRequiresPostgres18(keyName, entityType.DisplayName())
            );
        }

        // Keep "last property must be range-like" requirement, but allow Interval+conversion
        var lastProperty = key.Properties[^1];
        var typeMapping = lastProperty.FindTypeMapping();

        if (typeMapping is not NpgsqlRangeTypeMapping && typeMapping is not IntervalRangeMapping)
        {
            throw new InvalidOperationException(
                NpgsqlStrings.WithoutOverlapsRequiresRangeType(
                    keyName,
                    entityType.DisplayName(),
                    lastProperty.Name,
                    lastProperty.ClrType.ShortDisplayName()
                )
            );
        }
    }
}

It would be nice to have support for this.

[roji]

AI Triage

The below is an AI-generated analysis and may contain inaccuracies.

Classification

Feature request. The WithoutOverlaps() support was implemented for NpgsqlRange<T> types only; NodaTime range types (Interval → tstzrange, DateInterval → daterange) were not included in the initial implementation.

Analysis

The provider already recognizes NodaTime.Interval and NodaTime.DateInterval as range types in the TypeExtensions.IsRange() helper. However, the WithoutOverlaps validation and setup code only handles NpgsqlRangeTypeMapping, and the NodaTime range mappings (IntervalRangeMapping, DateIntervalRangeMapping) extend NpgsqlTypeMapping directly instead. This causes two issues:

1. Validation rejects NodaTime range types: NpgsqlModelValidator.ValidateWithoutOverlapsKey() checks typeMapping is not NpgsqlRangeTypeMapping, which fails for IntervalRangeMapping. The same applies to ValidatePeriodForeignKey().

2. No current value comparer set: SetRangeCurrentValueComparer() only handles NpgsqlRangeTypeMapping. Since NodaTime.Interval doesn't implement IComparable, the default CurrentValueComparerFactory rejects it when used as a key.

A fix would need to extend validation and comparer setup to recognize NodaTime range mappings. This is somewhat tricky since the NodaTime plugin is in a separate assembly; the core provider shouldn't directly reference NodaTime types. A possible approach would be to introduce a marker interface or base class for range-like mappings, or to check the store type string (e.g. tstzrange, daterange) rather than the mapping type.

Note that DateInterval would likely be affected by the same issue.

Not a regression

WithoutOverlaps() is a new feature added for PostgreSQL 18 support. NodaTime range types were never supported with it.

Related issues

• Supporting temporal data with WITHOUT OVERLAPS and PERIOD #2097 — original tracking issue for WithoutOverlaps / temporal constraints (now closed/implemented)