TimeOfDay issue in EF Core 6 and SQLite-CodePudding

I have a problem querying a SQLite database using EF Core 6.

The query is pretty simple:

_context.SomeTable
        .AsNoTracking()
        .Where(x => x.SomeDateTimeColumn.Value.TimeOfDay <= DateTime.Now.TimeOfDay)
        .ToList();

The exception message states that the query could not be translated:

The LINQ expression 'DbSet().Where(t => t.SomeDateTimeColumn.Value.TimeOfDay <= DateTime.Now.TimeOfDay)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation...

My problem is that, according to the documentation, DateTime.TimeOfDay should be supported by the SQLite database provider. See here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/functions#date-and-time-functions

Does anyone know the reason for this or could point me to what I'm missing?

CodePudding user response：

TimeSpan values are not yet supported by EF Core. Please upvote issue #18844.

Hmm, you might be able to do it using Ticks. But the precision might be kinda bad.

x.SomeDateTimeColumn.Value.Ticks % 864000000000 <= DateTime.Now.Ticks % 864000000000

For better precision, you can manually map the julianday function.

CodePudding user response：

I think it might be related to the way DateTime works.

Instead of using:

_context.SomeTable
    .AsNoTracking()
    .Where(x => x.SomeDateTimeColumn.Value.TimeOfDay <= DateTime.Now.TimeOfDay)
    .ToList();

Try this:

_context.SomeTable
    .AsNoTracking()
    .Where(x => x.SomeDateTimeColumn <= DateTime.Now)
    .ToList();