RETURNING used with MySQL although it is not supported

[arthurmeeh]

Steps to reproduce

Use 9.0.0-preview.2

Entity like this:

public class Entity
{
    public long Id { get; set; }
    // ...
}

Id is an auto-incrementing column (UseMySqlIdentityColumn())
Generated SQL when inserting a new record:

  INSERT INTO `Entities` (/**/)
      VALUES (/**/)
      RETURNING `Id`;

Using MySqlServerVersion.LatestSupportedServerVers

Further technical details

MySQL version: 9.1.0
Operating system: Linux (Docker)
Pomelo.EntityFrameworkCore.MySql version: 9.0.0-preview.2
Microsoft.AspNetCore.App version: -

Other details about my project setup:

[lauxjpn]

Pomelo.EntityFrameworkCore.MySql version: 9.0.0-preview.2

We will assume that you are using 9.0.0-preview.2.efcore.9.0.0.

MySQL version: 9.1.0

Be aware that MySQL 9.1.0 is a preview version.

---

I am unable to reproduce this issue with the following code:

Program.cs

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate;

public class IceCream
{
    public long IceCreamId { get; set; }
    public string Name { get; set; }
}

public class Context : DbContext
{
    public DbSet<IceCream> IceCreams { get; set; }

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

            optionsBuilder
                .UseMySql(
                    connectionString,
                    serverVersion)
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

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

            context.IceCreams.Add(new IceCream { Name = "Vanilla" });
            
            context.SaveChanges();
        }
        
        using (var context = new Context())
        {
            var iceCreams = context.IceCreams
                .ToList();
            
            Trace.Assert(iceCreams.Count == 1);
            Trace.Assert(iceCreams[0].Name == "Vanilla");
        }
    }
}

Output (SQL)

warn: 20.12.2024 02:22:57.520 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: 20.12.2024 02:22:58.467 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (37ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP DATABASE `Issue1962`;

info: 20.12.2024 02:23:02.817 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE `Issue1962`;

info: 20.12.2024 02:23:03.257 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER DATABASE CHARACTER SET utf8mb4;

info: 20.12.2024 02:23:03.296 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (37ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `IceCreams` (
          `IceCreamId` bigint NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      ) CHARACTER SET=utf8mb4;

info: 20.12.2024 02:23:04.939 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (35ms) [Parameters=[@p0='Vanilla' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      INSERT INTO `IceCreams` (`Name`)
      VALUES (@p0);
      SELECT `IceCreamId`
      FROM `IceCreams`
      WHERE ROW_COUNT() = 1 AND `IceCreamId` = LAST_INSERT_ID();

info: 20.12.2024 02:23:05.480 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`Name`
      FROM `IceCreams` AS `i`

The only way for RETURNING to be used is, if ServerVersion.Supports.Returning returns true, which is currently never the case for MySqlServerVersion (unless you override it), and only the case for MariaDbServerVersion if your server version is >= 10.5.0:

Pomelo.EntityFrameworkCore.MySql/src/EFCore.MySql/Infrastructure/ServerVersionSupport.cs

Line 88 in b1efe54

public virtual bool Returning => false;

Pomelo.EntityFrameworkCore.MySql/src/EFCore.MySql/Infrastructure/MariaDbServerVersion.cs

Line 90 in b1efe54

public override bool Returning => ServerVersion.Version >= new Version(10, 5, 0);

If MariaDbServerVersion with >= 10.5.0 is used, then the following SQL is generated:

Output (SQL) for MariaDB

warn: 20.12.2024 02:28:43.550 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: 20.12.2024 02:28:44.193 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP DATABASE `Issue1962`;

info: 20.12.2024 02:28:46.999 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE `Issue1962`;

info: 20.12.2024 02:28:47.356 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER DATABASE CHARACTER SET utf8mb4;

info: 20.12.2024 02:28:47.375 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `IceCreams` (
          `IceCreamId` bigint NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      ) CHARACTER SET=utf8mb4;

info: 20.12.2024 02:28:48.385 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (35ms) [Parameters=[@p0='Vanilla' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET AUTOCOMMIT = 1;
      INSERT INTO `IceCreams` (`Name`)
      VALUES (@p0)
      RETURNING `IceCreamId`;

info: 20.12.2024 02:28:48.844 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`Name`
      FROM `IceCreams` AS `i`

@arthurmeeh So it looks like you are not using MySqlServerVersion.LatestSupportedServerVersion.

[arthurmeeh]

I see. It was an oversight on my side, I wasn't using MySqlServerVersion.LatestSupportedServerVersion, because I was unintentionally reconfiguring the dbcontext by using a custom base class that overrode the configuration method.

PS: My company would gladly contribute donations to this project, because we have already been relying on it for many years :)

[lauxjpn]

I see. It was an oversight on my side, I wasn't using MySqlServerVersion.LatestSupportedServerVersion, because I was unintentionally reconfiguring the dbcontext by using a custom base class that overrode the configuration method.

Thanks for letting us know!

PS: My company would gladly contribute donations to this project, because we have already been relying on it for many years :)

Thanks, I am happy to hear that my work is appreciated. We have finally opened #1950 about it, so we can track progress about joining a sponsor program.