CODEWITHSUNDEEP
c#sql-serverlinqdateentity-framework-core
martinyyyy
martinyyyy

Reputation: 1660

How to query SQL Server using DateOnly with ef core

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.

Upvotes: 5

Views: 2769

Answers (2)

owns
owns

Reputation: 325

I ran into this problem today and couldn't change my where clause. I found a GitHub issue with the fix.

  1. Inherit from the <Provider>MemberTranslatorProvider class, e.g. Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerMemberTranslatorProvider
  2. Peek inside the above class. You should find a member translator like <Provider>DateTimeMemberTranslator, e.g. SqlServerDateTimeMemberTranslator.
  3. Copy DateTimeMemberTranslator into a new class, DateOnlyMemberTranslator and change the code accordingly (type check and remove all time code).
  4. In the new class from step #1, call the base constructor and do a call to AddTranslators to add the new translator!
  5. Finally, we have to use the translator provider by replacing the existing service: 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    base.OnConfiguring(optionsBuilder);
    optionsBuilder.ReplaceService<Microsoft.EntityFrameworkCore.Query.IMemberTranslatorProvider, MyProviderMemberTranslatorProvider>();
}

The github issue also recommends writing a plugin, but the above solution was super quick to write šŸ˜.

There is a warning for using efcore internal classes.

EF1001: Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerMemberTranslatorProvider is an internal API that supports the Entity Framework Core infrastructure and not subject to the same compatibility standards as public APIs. It may be changed or removed without notice in any release.
ā€” Microsoft

Upvotes: 0

Stig
Stig

Reputation: 2086

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

Upvotes: 4

Related Questions