After upgrading to 8.0.2 .SetAfterSaveBehavior(PropertySaveBehavior.Ignore) generates SQL that updates the column value

[Julia-Yakovleva]

The issue

After updating the package from version 7.0.0 to 8.0.2 a column that is configured with .SetAfterSaveBehavior(PropertySaveBehavior.Ignore) is modified when the entity is updated.

Steps to reproduce

Please see the working example with the previous package version here (pomelo-v7) and not-so-working example with the new package version here (pomelo-v8).

Configuration:

builder
    .Property(x => x.CreatedAt)
    .HasColumnName("createdAt")
    .Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore);

builder
    .Property(x => x.UpdatedAt)
    .HasColumnName("updatedAt")
    .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);

In our case parent entity Store is always updated together with its child entities Item. In real life scenario we have a more convoluted graph of child entities so to simplify the update we load parent from the context and swap its list of children to another list with detached entities.

var store = context.Stores.First(s => s.StoreId == storeId);

var items = CreateItems(request); // the list of new, updated and unchanged items
store.Items = items;

context.SaveChanges();

Child entities are created a particular way to utilize PropertySaveBehavior.Ignore usage:

public Item(string itemCode, string name)
{
    var now = DateTime.UtcNow.ToString();
    CreatedAt = now;
    UpdatedAt = now;
    ...
}

When a child entity isn't removed it exists in a context in two states: Added and Deleted

In a scenario where a child entity isn't removed we expect its UpdatedAt value to be updated and CreatedAt to stay the same due to .SetAfterSaveBehavior(PropertySaveBehavior.Ignore).

Expected SQL (with previous 7.0.0 package version):

UPDATE `item` SET `name` = @p0, `updatedAt` = @p1 -- createdAt isn't set :)
WHERE `itemCode` = @p2 AND `StoreId` = @p3;

SQL that gets generated by the latest 8.0.2 package version:

UPDATE `item` SET `createdAt` = @p0, `updatedAt` = @p1 -- createdAt is set :(
WHERE `itemCode` = @p2 AND `StoreId` = @p3;

Further technical details

MySQL version: 8.0, 8.3
Operating system: Windows 10
Pomelo.EntityFrameworkCore.MySql version: 8.0.2
Framework version: .NET 8

[lauxjpn]

I am able to reproduce the general issue with the following sample console app:

Program.cs

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate;

public class Store
{
    public int StoreId { get; set; }
    
    public string Name { get; set; }
    public string CreatedAt { get; set; }
    public string UpdatedAt { get; set; }

    public List<Item> Items { get; set; }
}

public class Item
{
    public string ItemCode { get; set; }
    public int StoreId { get; set; }

    public string Name { get; set; }
    public string CreatedAt { get; set; }
    public string UpdatedAt { get; set; }
}

public class Context : DbContext
{
    public DbSet<Store> Stores { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;Database=Issue1908";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(connectionString, serverVersion)
                // .UseSqlServer(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;Initial Catalog=Issue1908")
                // .UseNpgsql(@"server=127.0.0.1;port=5432;user id=postgres;password=postgres;database=Issue1908")
                // .UseSqlite(@"Data Source=Issue1908.db")
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Store>(
            entity =>
            {
                entity.HasKey(e => e.StoreId);

                entity.Property(e => e.StoreId);
                entity.Property(e => e.Name);
                entity.Property(e => e.CreatedAt)
                    .Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore); // Never generate `UPDATE` for CreatedAt
                entity.Property(e => e.UpdatedAt)
                    .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);  // Never generate `INSERT` for UpdatedAt

                entity.HasMany(e => e.Items)
                    .WithOne()
                    .HasForeignKey(e => e.StoreId);

                entity.Navigation(e => e.Items)
                    .AutoInclude();
            });

        modelBuilder.Entity<Item>(
            entity =>
            {
                entity.HasKey(e => new { e.StoreId, e.ItemCode });

                entity.Property(item => item.ItemCode);
                entity.Property(item => item.Name);
                entity.Property(e => e.CreatedAt)
                    .Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore); // Never generate `UPDATE` for CreatedAt
                entity.Property(e => e.UpdatedAt)
                    .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);  // Never generate `INSERT` for UpdatedAt

                entity.HasOne<Store>()
                    .WithMany(store => store.Items)
                    .HasForeignKey(e => e.StoreId);
            });
    }
}

internal static class Program
{
    private static void Main()
    {
        var lastChangeTrackerDebugView = string.Empty;
        
        using (var context = new Context())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Stores.Add(
                new Store
                {
                    Name = "Books",
                    CreatedAt = "2023-01-01",
                    UpdatedAt = "2023-01-01",
                    Items =
                    [
                        new Item
                        {
                            ItemCode = "lotr",
                            StoreId = 1,
                            Name = "The Fellowship of the Ring",
                            CreatedAt = "2023-01-01",
                            UpdatedAt = "2023-01-01",
                        }
                    ]
                });

            context.ChangeTracker.DetectChanges();
            lastChangeTrackerDebugView = context.ChangeTracker.DebugView.LongView.Trim().ReplaceLineEndings();

            context.SaveChanges();
        }

        using (var context = new Context())
        {
            var store = context.Stores.Single();
            
            Trace.Assert(store.StoreId == 1);
            Trace.Assert(store.Name == "Books");
            Trace.Assert(store.CreatedAt == "2023-01-01");
            Trace.Assert(store.UpdatedAt is null);
            Trace.Assert(store.Items is not null);
            Trace.Assert(store.Items.Count == 1);
            Trace.Assert(store.Items[0].StoreId == 1);
            Trace.Assert(store.Items[0].ItemCode == "lotr");
            Trace.Assert(store.CreatedAt == "2023-01-01");
            Trace.Assert(store.UpdatedAt is null);
            Trace.Assert(store.Items[0].Name == "The Fellowship of the Ring");
            
            Trace.Assert(
                Regex.Replace(lastChangeTrackerDebugView, @"^\s+", string.Empty, RegexOptions.Multiline) ==
                Regex.Replace(
"""
Item {StoreId: -2147482647, ItemCode: lotr} Added
    StoreId: -2147482647 PK FK Temporary
    ItemCode: 'lotr' PK
    CreatedAt: '2023-01-01'
    Name: 'The Fellowship of the Ring'
    UpdatedAt: '2023-01-01'
Store {StoreId: -2147482647} Added
    StoreId: -2147482647 PK Temporary
    CreatedAt: '2023-01-01'
    Name: 'Books'
    UpdatedAt: '2023-01-01'
    Items: [{StoreId: -2147482647, ItemCode: lotr}]
""", @"^\s+", string.Empty, RegexOptions.Multiline));
        }

        using (var context = new Context())
        {
            var store = context.Stores.Single();

            store.Name = "New Books";
            store.CreatedAt = "2024-02-02";
            store.UpdatedAt = "2024-02-02";
            store.Items =
            [
                new Item
                {
                    ItemCode = "lotr",
                    StoreId = 1,
                    Name = "The Two Towers",
                    CreatedAt = "2024-02-02",
                    UpdatedAt = "2024-02-02",
                }
            ];
            
            context.ChangeTracker.DetectChanges();
            lastChangeTrackerDebugView = context.ChangeTracker.DebugView.LongView.Trim().ReplaceLineEndings();
            
            context.SaveChanges();
        }

        using (var context = new Context())
        {
            var store = context.Stores.Single();
            
            Trace.Assert(store.StoreId == 1);
            Trace.Assert(store.Name == "New Books");
            Trace.Assert(store.CreatedAt == "2023-01-01");
            Trace.Assert(store.UpdatedAt == "2024-02-02");
            Trace.Assert(store.Items is not null);
            Trace.Assert(store.Items.Count == 1);
            Trace.Assert(store.Items[0].StoreId == 1);
            Trace.Assert(store.Items[0].ItemCode == "lotr");
            Trace.Assert(store.Items[0].Name == "The Two Towers");
            
            // I would consider the following unexpected. Either this is handled as a Delete+Insert operation pair,
            // in which case `CreatedAt` should contain `2024-02-02` and `UpdatedAt` should be `null`, or this is handled
            // as an Update operation (which it seems to be, since an `UPDATE` statement is generated), in which case
            // `CreatedAt` should contain the unchanged value of `2023-01-01` and `UpdatedAt` should be `2024-02-02`.
            Trace.Assert(store.Items[0].CreatedAt == "2024-02-02");
            Trace.Assert(store.Items[0].UpdatedAt == "2024-02-02");

            // Trace.Assert(store.Items[0].CreatedAt == "2024-02-02" && store.Items[0].UpdatedAt is null ||
            //              store.Items[0].CreatedAt == "2023-01-01" && store.Items[0].UpdatedAt == "2024-02-02");
            
            Trace.Assert(
                Regex.Replace(lastChangeTrackerDebugView, @"^\s+", string.Empty, RegexOptions.Multiline) == 
                Regex.Replace(
"""
Item (Shared) {StoreId: 1, ItemCode: lotr} Added
    StoreId: 1 PK FK
    ItemCode: 'lotr' PK
    CreatedAt: '2024-02-02'
    Name: 'The Two Towers'
    UpdatedAt: '2024-02-02'
Item (Shared) {StoreId: 1, ItemCode: lotr} Deleted
    StoreId: 1 PK FK
    ItemCode: 'lotr' PK
    CreatedAt: '2023-01-01'
    Name: 'The Fellowship of the Ring'
    UpdatedAt: <null>
Store {StoreId: 1} Modified
    StoreId: 1 PK
    CreatedAt: '2024-02-02' Modified Originally '2023-01-01'
    Name: 'New Books' Modified Originally 'Books'
    UpdatedAt: '2024-02-02' Modified Originally <null>
    Items: [{StoreId: 1, ItemCode: lotr}]
""", @"^\s+", string.Empty, RegexOptions.Multiline));
        }
    }
}

Output (SQL)

warn: 18.04.2024 12:35:13.306 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 18.04.2024 12:35:13.667 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (30ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      DROP DATABASE `Issue1908`;                                                                                        
info: 18.04.2024 12:35:13.875 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                 
      CREATE DATABASE `Issue1908`;                                                                                      
info: 18.04.2024 12:35:14.012 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER DATABASE CHARACTER SET utf8mb4;
info: 18.04.2024 12:35:14.043 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (30ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Stores` (
          `StoreId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `CreatedAt` longtext CHARACTER SET utf8mb4 NULL,
          `UpdatedAt` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_Stores` PRIMARY KEY (`StoreId`)
      ) CHARACTER SET=utf8mb4;
info: 18.04.2024 12:35:14.076 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Item` (
          `ItemCode` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
          `StoreId` int NOT NULL,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `CreatedAt` longtext CHARACTER SET utf8mb4 NULL,
          `UpdatedAt` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_Item` PRIMARY KEY (`StoreId`, `ItemCode`),
          CONSTRAINT `FK_Item_Stores_StoreId` FOREIGN KEY (`StoreId`) REFERENCES `Stores` (`StoreId`) ON DELETE CASCADE
      ) CHARACTER SET=utf8mb4;
info: 18.04.2024 12:35:14.307 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (21ms) [Parameters=[@p0='2023-01-01' (Size = 4000), @p1='Books' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      INSERT INTO `Stores` (`CreatedAt`, `Name`)
      VALUES (@p0, @p1);
      SELECT `StoreId`
      FROM `Stores`
      WHERE ROW_COUNT() = 1 AND `StoreId` = LAST_INSERT_ID();
info: 18.04.2024 12:35:14.337 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (3ms) [Parameters=[@p2='lotr' (Nullable = false) (Size = 255), @p3='1', @p4='2023-01-01' (Size = 4000), @p5='The Fellowship of the Ring' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET AUTOCOMMIT = 1;
      INSERT INTO `Item` (`ItemCode`, `StoreId`, `CreatedAt`, `Name`)
      VALUES (@p2, @p3, @p4, @p5);
info: 18.04.2024 12:35:14.715 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`
info: 18.04.2024 12:35:18.550 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`
info: 18.04.2024 12:35:18.572 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[@p3='lotr' (Nullable = false) (Size = 255), @p4='1', @p0='2024-02-02' (Size = 4000), @p1='The Two Towers' (Size = 4000), @p2='2024-02-02' (Size = 4000), @p7='1', @p5='New Books' (Size =
 4000), @p6='2024-02-02' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      UPDATE `Item` SET `CreatedAt` = @p0, `Name` = @p1, `UpdatedAt` = @p2
      WHERE `ItemCode` = @p3 AND `StoreId` = @p4;
      SELECT ROW_COUNT();

      UPDATE `Stores` SET `Name` = @p5, `UpdatedAt` = @p6
      WHERE `StoreId` = @p7;
      SELECT ROW_COUNT();
info: 18.04.2024 12:35:18.590 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`

However, I am getting the same behavior consistently for EF Core 7.0.x and 8.0.x (I did not try other versions; be aware that I explicitly added an Item.StoreId property for referential integrity).

This issue seems to be reproducible with any EF Core provider, so it is an EF Core specific issue, not a Pomelo one.

That being said, the current behavior seems to me to be unexpected as well (see the comments in the latest using block in my sample code).

/cc @roji @ajcvickers

[Julia-Yakovleva]

Thank you for having a look!

I'm with you on that the states of the object seem to be a bit off, however I thought there was an explicitly implemented workaround since the combination Microsoft.EntityFrameworkCore 7.0.15 + Pomelo.EntityFrameworkCore.MySql 7.0.0. generated expected SQL.

[lauxjpn]

With the code I posted above, I am getting the same unexpected EF Core behavior in both, EF Core 7.0.18 and 8.0.4.

@Julia-Yakovleva Feel free to adjust the code I posted in such a way that it works in 7.0.18 but not in 8.0.4, and repost it.

(Be aware of the mentioned changes I made to your original code.)

[Julia-Yakovleva]

Okay, I've got some results...

The only thing I've updated in the posted code was making UpdatedAt nullable for both entities.
public string UpdatedAt { get; set; } -> public string? UpdatedAt { get; set; }

New lib version

References:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.4" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="8.0.2" />

Generated SQL:

UPDATE `Item` SET `CreatedAt` = @p0, `Name` = @p1, `UpdatedAt` = @p2
WHERE `ItemCode` = @p3 AND `StoreId` = @p4;
SELECT ROW_COUNT();

UPDATE `Stores` SET `Name` = @p5, `UpdatedAt` = @p6
WHERE `StoreId` = @p7;
SELECT ROW_COUNT();

DB:

Debug:

Previous lib version

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.18" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="7.0.0" />

Generated SQL:

UPDATE `Item` SET `Name` = @p0, `UpdatedAt` = @p1
WHERE `ItemCode` = @p2 AND `StoreId` = @p3;
SELECT ROW_COUNT();

UPDATE `Stores` SET `Name` = @p4, `UpdatedAt` = @p5
WHERE `StoreId` = @p6;
SELECT ROW_COUNT();

DB:

Debug:

So for me the older version works as expected. Not sure if I'm missing something.

[lauxjpn]

The only thing I've updated in the posted code was making UpdatedAt nullable for both entities.

Hmm, doing that runs the code in the same way as before for me in both versions (I am also assuming a #nullable enable context).

Output (SQL)

warn: 25.04.2024 11:55:20.919 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 25.04.2024 11:55:21.456 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (39ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      DROP DATABASE `Issue1908`;                                                                                        
info: 25.04.2024 11:55:21.767 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      CREATE DATABASE `Issue1908`;                                                                                      
info: 25.04.2024 11:55:21.961 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER DATABASE CHARACTER SET utf8mb4;
info: 25.04.2024 11:55:22.000 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (37ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Stores` (
          `StoreId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NOT NULL,
          `CreatedAt` longtext CHARACTER SET utf8mb4 NULL,
          `UpdatedAt` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_Stores` PRIMARY KEY (`StoreId`)
      ) CHARACTER SET=utf8mb4;
info: 25.04.2024 11:55:22.054 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (53ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Item` (
          `ItemCode` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
          `StoreId` int NOT NULL,
          `Name` longtext CHARACTER SET utf8mb4 NOT NULL,
          `CreatedAt` longtext CHARACTER SET utf8mb4 NULL,
          `UpdatedAt` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_Item` PRIMARY KEY (`StoreId`, `ItemCode`),
          CONSTRAINT `FK_Item_Stores_StoreId` FOREIGN KEY (`StoreId`) REFERENCES `Stores` (`StoreId`) ON DELETE CASCADE
      ) CHARACTER SET=utf8mb4;
info: 25.04.2024 11:55:22.426 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (32ms) [Parameters=[@p0='2023-01-01' (Size = 4000), @p1='Books' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      INSERT INTO `Stores` (`CreatedAt`, `Name`)
      VALUES (@p0, @p1);
      SELECT `StoreId`
      FROM `Stores`
      WHERE ROW_COUNT() = 1 AND `StoreId` = LAST_INSERT_ID();
info: 25.04.2024 11:55:22.471 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (5ms) [Parameters=[@p2='lotr' (Nullable = false) (Size = 255), @p3='1', @p4='2023-01-01' (Size = 4000), @p5='The Fellowship of the Ring' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeo
ut='30']
      SET AUTOCOMMIT = 1;
      INSERT INTO `Item` (`ItemCode`, `StoreId`, `CreatedAt`, `Name`)
      VALUES (@p2, @p3, @p4, @p5);
info: 25.04.2024 11:55:22.984 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`
info: 25.04.2024 11:55:23.039 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`
info: 25.04.2024 11:55:23.065 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[@p3='lotr' (Nullable = false) (Size = 255), @p4='1', @p0='2024-02-02' (Size = 4000), @p1='The Two Towers' (Nullable = false) (Size = 4000), @p2='2024-02-02' (Size = 4000), @p7='1', @p5=
'New Books' (Nullable = false) (Size = 4000), @p6='2024-02-02' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      UPDATE `Item` SET `CreatedAt` = @p0, `Name` = @p1, `UpdatedAt` = @p2
      WHERE `ItemCode` = @p3 AND `StoreId` = @p4;
      SELECT ROW_COUNT();

      UPDATE `Stores` SET `Name` = @p5, `UpdatedAt` = @p6
      WHERE `StoreId` = @p7;
      SELECT ROW_COUNT();
info: 25.04.2024 11:55:23.090 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`

@Julia-Yakovleva Please post the entire code. Thanks!

[Julia-Yakovleva]

Making UpdatedAt nullable was mandatory as otherwise there was an exception due to #nullable enable just like you've mentioned:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> MySqlConnector.MySqlException (0x80004005): Field 'UpdatedAt' doesn't have a default value

I'm not really sure what's the difference between your and my setup. I've cleared cashed Pomelo package, removed the volume and recreated a mysql container via compose. Got the same result.

Here's a new project with the full copy of the code you've posted. The last commit downgrades package versions which makes the code work as expected for me.

@lauxjpn , thank you for keeping up looking into this issue. Please let me know if there's anything else for me to check or debug.

Output

warn: 29-Apr-24 11:21:02.317 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 29-Apr-24 11:21:02.599 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (26ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP DATABASE `eftest`;
info: 29-Apr-24 11:21:10.004 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE `eftest`;
info: 29-Apr-24 11:21:10.098 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER DATABASE CHARACTER SET utf8mb4;
info: 29-Apr-24 11:21:10.118 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (20ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Stores` (
          `StoreId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NOT NULL,
          `CreatedAt` longtext CHARACTER SET utf8mb4 NOT NULL,
          `UpdatedAt` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_Stores` PRIMARY KEY (`StoreId`)
      ) CHARACTER SET=utf8mb4;
info: 29-Apr-24 11:21:10.141 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (22ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Item` (
          `ItemCode` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
          `StoreId` int NOT NULL,
          `Name` longtext CHARACTER SET utf8mb4 NOT NULL,
          `CreatedAt` longtext CHARACTER SET utf8mb4 NOT NULL,
          `UpdatedAt` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_Item` PRIMARY KEY (`StoreId`, `ItemCode`),
          CONSTRAINT `FK_Item_Stores_StoreId` FOREIGN KEY (`StoreId`) REFERENCES `Stores` (`StoreId`) ON DELETE CASCADE
      ) CHARACTER SET=utf8mb4;
info: 29-Apr-24 11:21:11.965 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (18ms) [Parameters=[@p0='2023-01-01' (Nullable = false) (Size = 4000), @p1='Books' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      INSERT INTO `Stores` (`CreatedAt`, `Name`)
      VALUES (@p0, @p1);
      SELECT `StoreId`
      FROM `Stores`
      WHERE ROW_COUNT() = 1 AND `StoreId` = LAST_INSERT_ID();
info: 29-Apr-24 11:21:11.987 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (3ms) [Parameters=[@p2='lotr' (Nullable = false) (Size = 255), @p3='1', @p4='2023-01-01' (Nullable = false) (Size = 4000), @p5='The Fellowship of the Ring' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET AUTOCOMMIT = 1;
      INSERT INTO `Item` (`ItemCode`, `StoreId`, `CreatedAt`, `Name`)
      VALUES (@p2, @p3, @p4, @p5);
info: 29-Apr-24 11:21:12.277 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`
info: 29-Apr-24 11:21:12.309 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`
info: 29-Apr-24 11:21:14.597 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[@p2='lotr' (Nullable = false) (Size = 255), @p3='1', @p0='The Two Towers' (Nullable = false) (Size = 4000), @p1='2024-02-02' (Size = 4000), @p6='1', @p4='New Books' (Nullable = false) (Size = 4000), @p5='2024-02-02' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      UPDATE `Item` SET `Name` = @p0, `UpdatedAt` = @p1
      WHERE `ItemCode` = @p2 AND `StoreId` = @p3;
      SELECT ROW_COUNT();

      UPDATE `Stores` SET `Name` = @p4, `UpdatedAt` = @p5
      WHERE `StoreId` = @p6;
      SELECT ROW_COUNT();
info: 29-Apr-24 11:21:15.674 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `t`.`StoreId`, `t`.`CreatedAt`, `t`.`Name`, `t`.`UpdatedAt`, `i`.`StoreId`, `i`.`ItemCode`, `i`.`CreatedAt`, `i`.`Name`, `i`.`UpdatedAt`
      FROM (
          SELECT `s`.`StoreId`, `s`.`CreatedAt`, `s`.`Name`, `s`.`UpdatedAt`
          FROM `Stores` AS `s`
          LIMIT 2
      ) AS `t`
      LEFT JOIN `Item` AS `i` ON `t`.`StoreId` = `i`.`StoreId`
      ORDER BY `t`.`StoreId`, `i`.`StoreId`

[ajcvickers]

@lauxjpn @Julia-Yakovleva I spent some time investigating this. The repro posted above has a race condition, which means the issue only shows up some of the time on any given version--this might be why you have been getting conflicting results.

This looks like a change in behavior that started with 7.0.3. It is not MySQL-specific. I have filed an issue here: dotnet/efcore#33653