The order of ExecuteUpdate setters is not preserved

[roji]

For the following ExecuteUpdate:

await context.Posts
    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.Content, b => b.Content + " ( Title was " + b.Title  + ")")
        .SetProperty(b => b.Title, b => "New Title"));

... we generate the following SQL:

UPDATE [p]
SET [p].[Title] = N'New Title',
        [p].[Content] = [p].[Content] + N' ( Title was ' + [p].[Title] + N')'
FROM [Posts] AS [p]

Note that the SQL order of the setters is reversed; this is important, as there can be a dependency between the setters. We should preserve the original LINQ ordering.

Originally flagged in PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#1776

Full repro

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

await context.Posts
    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.Content, b => b.Content + " ( Title was " + b.Title  + ")")
        .SetProperty(b => b.Title, b => "New Title"));

public class BlogContext : DbContext
{
    public DbSet<Post> Posts { get; set; }

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

public class Post
{
    public int Id { get; set; }
    public string Content { get; set; }
    public string Title { get; set; }
}

[roji]

The plot thickens... It turns out that in most databases, the setters always see the row as it was before any updates were made to it; this means that the order of setters is irrelevant:

DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int);
INSERT INTO foo (a, b) VALUES (0, 0);

UPDATE foo SET a = 10, b = a + 1;
SELECT b FROM foo;

The above returns 1 rather than 11 for most databases (SQL Server, PostgreSQL, SQLite). However, MySQL and MariaDB return 11, meaning that the previous setter is taken into account when evaluating the later setter; ordering matters there. Note that current EF simply inverses the setters, so as a workaround users can simply inverse themselves, starting with the last.

I've taken a look, and this is fixed in #35257, which should get merged shortly. @lauxjpn it would be great if you could add a test on your side to confirm this, something like:

[ConditionalTheory]
[MemberData(nameof(IsAsyncData))]
public virtual Task Update_Where_set_ordering_is_preserved(bool async)
    => TestHelpers.ExecuteWithStrategyInTransactionAsync(
        Fixture.CreateContext, Fixture.UseTransaction,
        async context =>
        {
            var updated = await context.Set<Customer>().ExecuteUpdateAsync(
                setters => setters
                    .SetProperty(c => c.ContactName, "X")
                    .SetProperty(c => c.ContactTitle, c => c.ContactName + "Y"));
            Assert.True(updated > 0);
            Assert.Equal(updated, await context.Set<Customer>().CountAsync(c => c.ContactTitle == "XY"));

            updated = await context.Set<Customer>().ExecuteUpdateAsync(
                setters => setters
                    .SetProperty(c => c.City, "Y")
                    .SetProperty(c => c.ContactName, c => c.City + "X"));
            Assert.True(updated > 0);
            Assert.Equal(updated, await context.Set<Customer>().CountAsync(c => c.ContactName == "YX"));
        });

[ChrisJollyAU]

@roji MS Access returns 1 so no issue there either