How to query SQL Server using DateOnly with ef core-CodePudding

I'm using .NET 6 and EF Core 6 with SQL Server and want to use the new DateOnly type.

I've been able to read and write data to the database using this converter however querying the table doesn't work because Linq doesn't know how to translate DateOnly.

Converter registration in DbContext:

protected override void ConfigureConventions
(ModelConfigurationBuilder builder)        
{
    builder.Properties<DateOnly>()                
        .HaveConversion<DateOnlyConverter>()                
        .HaveColumnType("date");
    builder.Properties<DateOnly?>()                
        .HaveConversion<NullableDateOnlyConverter>()                
        .HaveColumnType("date");        
}

Example

    public XXXXByDateSpec(DateOnly validFrom)
    {
        Query.Where(x => x.ValidFrom.Year <= validFrom.Year);
    }

But this results in the following exception.

System.InvalidOperationException: The LINQ expression 'DbSet().Where(c => c.ValidFrom.Year <= __validFrom_Year_1)' could not be translated.
Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

And when I'm trying to first parse it to a DateTime results in a similar error.

Query.Where(x => DateTime.Parse(x.ValidFrom.ToString()).Year <= DateTime.Parse(validFrom.ToString()).Year);

System.InvalidOperationException: The LINQ expression 'DbSet().Where(c => DateTime.Parse(c.ValidFrom.ToString()).Year <= __Parse_Year_0)' could not be translated. Additional information: Translation of method 'object.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.
Translation of method 'object.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

How can I tell Linq to do the same like EF Core and before translating the code to SQL do a type conversion to DateTime? Is this possible?

Or how can I register the ToString() call to Linq? The links from the exception don't really help me out.

CodePudding user response：

This should work, though not as readable. This benefits from using index if present. Also a contract with DateOnly as the parameter is confusing if only the year part is used.

 public XXXXByDateSpec(int year)
 {
    Query.Where(x => x.ValidFrom < new DateOnly(year   1, 1, 1);
 }