How can I properly filter nullable DateOnly?

Question

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);
}

Zoran Horvat · Answer 1 · 2022-06-07T11:36:34.673

1

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.

edited Jun 07 '22 at 11:36

answered Jun 07 '22 at 11:26

Zoran Horvat

10,924
3
31
43

Will that return entities where `Property1 == null`? I'm not sure if I can take this approach, as my query is sent to sql to be processed, rather than building the query in EF/linq – Bassie Jun 07 '22 at 11:28
Maybe I have misinterpreted your question - when you say "filter", do you mean to keep them, or to remove them? Do you want your query to return all entities with smaller date *or* a null date? – Zoran Horvat Jun 07 '22 at 11:34
I have edited code to work in this way, and added an explanation why the database is filtering out entities with `NULL` property. – Zoran Horvat Jun 07 '22 at 11:46

score 1 · Answer 2 · answered Jun 07 '22 at 11:29

1

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);

answered Jun 07 '22 at 11:29

YungDeiza

3,128
1
7
32

How can I properly filter nullable DateOnly?

2 Answers2