With the code fragment below, I would like to update the status properties of multiple database records, for example bookings.

Each record has the properties StatusCurrent and StatusBefore. I would like to change the property StatusCurrent with a new value and set the old value of StatusCurrent to the property StatusBefore.

For example:

StatusCurrent = 2, StatusBefore = 1  

The new value of StatusCurrent might be 3 for example, so the expected situation should be:

StatusCurrent = 3, StatusBefore = 2

Therefore I used the code below, but the resulting situation is not the expected one:

• Expected result: StatusCurrent = 3, StatusBefore = 2
• Current result: StatusCurrent = 3, StatusBefore = 3

var updatedRows = await _dbContext.Bookings
    .Where(b => bookingIds.Contains(b.BookingId))
    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.StatusBefore, b => b.StatusCurrent)
        .SetProperty(b => b.StatusCurrent, (int)newStatus));

As described in the Microsoft documentation the expected situation should be resulting, but it didn't.

Do you have any idea?

Thanks a lot in advance,
Armand

With the code fragment below, I would like to update the status properties of multiple database records, for example bookings.

Each record has the properties StatusCurrent and StatusBefore. I would like to change the property StatusCurrent with a new value and set the old value of StatusCurrent to the property StatusBefore.

For example:

StatusCurrent = 2, StatusBefore = 1  

The new value of StatusCurrent might be 3 for example, so the expected situation should be:

StatusCurrent = 3, StatusBefore = 2

Therefore I used the code below, but the resulting situation is not the expected one:

• Expected result: StatusCurrent = 3, StatusBefore = 2
• Current result: StatusCurrent = 3, StatusBefore = 3

var updatedRows = await _dbContext.Bookings
    .Where(b => bookingIds.Contains(b.BookingId))
    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.StatusBefore, b => b.StatusCurrent)
        .SetProperty(b => b.StatusCurrent, (int)newStatus));

As described in the Microsoft documentation the expected situation should be resulting, but it didn't.

Do you have any idea?

Thanks a lot in advance,
Armand

• c#
• mysql
• entity-framework-core
• mariadb
• bulkupdate

• 4 Which DBMS is this? MySQL/MariaDB has a known issue with column SET ordering. Can you show the generated SQL for this? – Charlieface Commented Feb 5 at 16:36
• Was curious about this and tried it with SQL Server and it worked as expected so either it is RDBMS-specific behaviour difference or you have an unexpected bug such as a pending change in the change tracker for the entity in question getting persisted. – Steve Py Commented Feb 5 at 22:34
• Thanks a lot for these useful hints! @Charlieface @Steve Py I got the SQL (MariaDB) that was generated and saw the problem. The order of the SET statements are changed as @Charlieface said. This is the LINQ query: ... .ExecuteUpdateAsync(s => s .SetProperty(m => m.StatusBefore, m => m.StatusCurrent) .SetProperty(m => m.StatusCurrent, (int)status) );` This is the generated SQL: ... SET z.StatusCurrent = CAST(@__status_1 AS signed), z.StatusBefore = z.StatusAktuell My solution: I changed the order in LINQ and it works. – Armand Commented Feb 6 at 14:23
• @StevePy See dev.mysql.com/doc/refman/8.4/en/ansi-diff-update.html and dba.stackexchange.com/a/297590/220697 and stackoverflow.com/questions/2203202/… – Charlieface Commented Feb 6 at 14:41

1 Answer 1

MySQL and MariaDB have a known bug/misfeature, that there is no read/write phase separation in UPDATE statements. The clauses are simply executed top-down on the row itself, so when you write column1 = column2, column2 = column1 you end up with the same value in both, rather than a swap.

EF Core generates the SET clauses in reverse order from the LINQ, so in this case you need to place them the other way round, so StatusBefore can grab the StatusCurrent data before it's overwritten.

    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.StatusCurrent, (int)newStatus)
        .SetProperty(b => b.StatusBefore, b => b.StatusCurrent)
    );

Whatever you are thinking about this "feature", I agree with.

MariaDB 10.3.5 and up now offers the SIMULTANEOUS_ASSIGNMENT option.

Setting this makes the SET part of the UPDATE statement evaluate all assignments simultaneously, not left-to-right.

I strongly suggest you turn this option on.