How can I properly filter nullable DateOnly?-CodePudding

I have successfully configured a DateOnly property on my EF 6 entity as below

Everything is working great, except for the fact that the property is nullable. The issue occurs when I try to filter entities where Prperty1 < [some-date], in that EF does not consider null to be less than anything, so it is excluded from the results

How can I get EF to understand that null is basically less than or equivalent to DateOnly.MinValue when filtering on that property?

I already tried replacing DateOnly with DateOnly? in my converter, but it makes no difference

public class MyEntity
{
    public DateOnly? Property1 { get; private set; }
}

Configuration:

builder
    .Property(x => x.Property1)
    .HasConversion(DateOnlyRelationalTypeMapping.Converter)
    .HasColumnType("date");

Relational type mapping:

public sealed class DateOnlyRelationalTypeMapping : RelationalTypeMapping
{
    internal DateOnlyRelationalTypeMapping() 
        : this(
            new RelationalTypeMappingParameters(
                new CoreTypeMappingParameters(
                    typeof(DateOnly),
                    Converter),
                "date"))
    {
    }

    private DateOnlyRelationalTypeMapping(RelationalTypeMappingParameters parameters)
        : base(parameters)
    {
    }

    public static new readonly ValueConverter<DateOnly, DateTime> Converter =
        new(
            clrValue => clrValue.ToDateTime(TimeOnly.MinValue),
            dbValue => DateOnly.FromDateTime(dbValue));

    protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters) 
        => new DateOnlyRelationalTypeMapping(parameters);
}

CodePudding user response：

The comparison should be: is the DateOnly property is null OR less than a specified fate i.e.

.....Where(x => x.Property1 == null || x.Property1 < targetDate);

CodePudding user response：

You can add that check yourself.

Instead of relying on EF:

dbContext.Entities.Where(e => e.Property1 < someDate); // does not work

add the constraint that entities with null date are also included:

dbContext.Entities.Where(e => e.Property1 != null || e.Property1 < someDate);

Please note that this behavior has nothing to do with EF. That is how SQL compares values. A DB NULL compared to anything returns Boolean result NULL - neither true, nor false. When a Boolean check is included in the SQL WHERE clause, anything that evaluates to a value other than true (that stands for NULL, too) will be excluded from the output.