Translate TimeOnly.AddHours, AddMinutes on SQLite

[ajcvickers]

No description provided.

[ErikEJ]

There is no "AddSeconds" 😄

[roji]

@ajcvickers didn't I already do this in #30109, are you seeing issues? Here's the test for TimeOnly.AddHours.

[ajcvickers]

Looks like it works on SQL Server for normal columns, but fails with JSON columns:

Unhandled exception. System.InvalidOperationException: The LINQ expression 'JsonQueryExpression(s.OpeningHours, $)
    .AsQueryable()' 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.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelectMany(ShapedQueryExpression source, LambdaExpression collectionSelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.ExecuteUpdateAsync[TSource](IQueryable`1 source, Expression`1 setPropertyCalls, CancellationToken cancellationToken)
   at Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 42
   at Program.<Main>()

And fails with SQLite:

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<School>()
    .SelectMany(
        collectionSelector: s => EF.Property<List<OpeningHours>>(s, "OpeningHours")
            .AsQueryable(),
        resultSelector: (s, c) => new TransparentIdentifier<School, OpeningHours>(
            Outer = s,
            Inner = c
        ))
    .Where(ti => (int)ti.Inner.DayOfWeek == 5)
    .Select(ti => ti.Inner)
    .ExecuteUpdate(s => s.SetProperty<TimeOnly>(
        propertyExpression: t => t.OpensAt,
        valueExpression: t => t.OpensAt.AddHours(-1)))' could not be translated. Additional information: The following 'SetProperty' failed to translate: 'SetProperty(t => t.OpensAt, t => t.OpensAt.AddHours(-1))'. Translation of method 'System.TimeOnly.AddHours' failed. If this method can be mapped to your cust
om function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.ExecuteUpdateAsync[TSource](IQueryable`1 source, Expression`1 setPropertyCalls, CancellationToken cancellationToken)
   at Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 42
   at Program.<Main>()

But note that in triage the team felt that these translations were a pit-of-failure anyway. :-)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

#nullable disable
// ReSharper disable once HeapView.ObjectAllocation.Evident
#pragma warning disable CA1050

public class School
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public DateOnly Founded { get; set; }
    public List<OpeningHours> OpeningHours { get; } = new();
}

[Owned]
public class OpeningHours
{
    public DayOfWeek DayOfWeek { get; set; }
    public TimeOnly OpensAt { get; set; }
    public TimeOnly ClosesAt { get; set; }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new SomeDbContext())
        {
            await context.Database.EnsureDeletedAsync();
            await context.Database.EnsureCreatedAsync();

            await context.SaveChangesAsync();
        }

        using (var context = new SomeDbContext())
        {
            await context.Schools
                .SelectMany(e => e.OpeningHours)
                .Where(e => e.DayOfWeek == DayOfWeek.Friday)
                .ExecuteUpdateAsync(s => s.SetProperty(t => t.OpensAt, t => t.OpensAt.AddHours(-1)));
        }
    }
}

public class SomeDbContext : DbContext
{
    public DbSet<School> Schools => Set<School>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlite(@"Data Source=c:\local\test.db")
            //.UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //modelBuilder.Entity<School>().OwnsMany(e => e.OpeningHours).ToJson();
    }
}

[roji]

Looks like it works on SQL Server for normal columns, but fails with JSON columns:

Right... Since JSON doesn't have actual date/time types, that may be expected - we can look into it to confirm.

And fails with SQLite:

Yeah - I think TimeSpan/TimeOnly translations are tracked by #18844, since they require custom functions (no built-in functions for this in SQLite). There's also #19632 and #25103 (we may want to consolidate).

But note that in triage the team felt that these translations were a pit-of-failure anyway. :-)

Interesting :) Because of the day wraparound? If so, doesn't that work the same way both in .NET and in the database?

[ajcvickers]

Interesting :) Because of the day wraparound? If so, doesn't that work the same way both in .NET and in the database?

I think so. But maybe we shouldn't perpetuate a bad pattern...

[roji]

OK. FWIW these translations already do exist in the PG provider (since 6.0), we can have a design discussion on them... I haven't thought about these APIs specifically, but they don't seem that bad to me...

[ajcvickers]

Duplicate of #22950