Table alias regression on ExecuteUpdate on SQL Server

[ajcvickers]

This worked on 8.0 on SQL Server, but fails on the current daily build of 9. (Note that it always failed on SQLite and with JSON on SQL Server.)

await context.Schools
    .SelectMany(e => e.OpeningHours)
    .Where(e => e.DayOfWeek == DayOfWeek.Friday)
    .ExecuteUpdateAsync(s => s.SetProperty(t => t.OpensAt, t => t.OpensAt!.Value.AddHours(-1)));

fail: 6/9/2024 11:32:01.325 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command) 
      Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      UPDATE [o]
      SET [o0].[OpensAt] = DATEADD(hour, CAST(-1.0E0 AS int), [o].[OpensAt])
      FROM [Schools] AS [s]
      INNER JOIN [OpeningHours] AS [o] ON [s].[Id] = [o].[SchoolId]
      WHERE [o].[DayOfWeek] = 5

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): The multi-part identifier "o0.OpensAt" could not be bound.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteNonQueryAsync>b__193_1(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Program.<Main>$(String[] args) in D:\code\AllTogetherNow\One\Program.cs:line 63
   at Program.<Main>(String[] args)
ClientConnectionId:eef4fd5d-d4b2-49e4-b6fd-376987ddb75e
Error Number:4104,State:1,Class:16

Repro

using (var context = new AppDbContext())
{
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();

    context.AddRange(
        new School
        {
            Name = "Stowe School",
            Founded = new(1923, 5, 11),
            Terms =
            {
                new() { Name = "Michaelmas", FirstDay = new(2022, 9, 7), LastDay = new(2022, 12, 16) },
                new() { Name = "Lent", FirstDay = new(2023, 1, 8), LastDay = new(2023, 3, 24) },
                new() { Name = "Summer", FirstDay = new(2023, 4, 18), LastDay = new(2023, 7, 8) }
            },
            OpeningHours =
            {
                new(DayOfWeek.Sunday, null, null),
                new(DayOfWeek.Monday, new(8, 00), new(18, 00)),
                new(DayOfWeek.Tuesday, new(8, 00), new(18, 00)),
                new(DayOfWeek.Wednesday, new(8, 00), new(18, 00)),
                new(DayOfWeek.Thursday, new(8, 00), new(18, 00)),
                new(DayOfWeek.Friday, new(8, 00), new(18, 00)),
                new(DayOfWeek.Saturday, new(8, 00), new(17, 00))
            }
        },
        new School
        {
            Name = "Farr High School",
            Founded = new(1964, 5, 1),
            Terms =
            {
                new() { Name = "Autumn", FirstDay = new(2022, 8, 16), LastDay = new(2022, 12, 23) },
                new() { Name = "Winter", FirstDay = new(2023, 1, 9), LastDay = new(2023, 3, 31) },
                new() { Name = "Summer", FirstDay = new(2023, 4, 17), LastDay = new(2023, 6, 29) }
            },
            OpeningHours =
            {
                new(DayOfWeek.Sunday, null, null),
                new(DayOfWeek.Monday, new(8, 45), new(15, 35)),
                new(DayOfWeek.Tuesday, new(8, 45), new(15, 35)),
                new(DayOfWeek.Wednesday, new(8, 45), new(15, 35)),
                new(DayOfWeek.Thursday, new(8, 45), new(15, 35)),
                new(DayOfWeek.Friday, new(8, 45), new(12, 50)),
                new(DayOfWeek.Saturday, null, null)
            }
        });

    await context.SaveChangesAsync();
}

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


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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
             .UseSqlServer("Data Source=localhost;Database=BuildBlogs;Integrated Security=True;Trust Server Certificate=True;ConnectRetryCount=0")
            .LogTo(Console.WriteLine, LogLevel.Debug)
            .EnableSensitiveDataLogging();
}

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

public class Term
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public DateOnly FirstDay { get; set; }
    public DateOnly LastDay { get; set; }
    public School School { get; set; } = null!;
}

[Owned]
public class OpeningHours
{
    public OpeningHours(DayOfWeek dayOfWeek, TimeOnly? opensAt, TimeOnly? closesAt)
    {
        DayOfWeek = dayOfWeek;
        OpensAt = opensAt;
        ClosesAt = closesAt;
    }

    public DayOfWeek DayOfWeek { get; private set; }
    public TimeOnly? OpensAt { get; set; }
    public TimeOnly? ClosesAt { get; set; }
}

[roji]

Even more minimal repro

using var context = new AppDbContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

await context.Schools
    .SelectMany(e => e.OpeningHours)
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Foo, 8));

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test3;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class School
{
    public int Id { get; set; }
    public List<OpeningHours> OpeningHours { get; } = new();
}

[Owned]
public class OpeningHours
{
    public int Foo { get; set; }
}

Confirmed the regression is in commit 1549daa (#32812)