To input a sytem type to Annotation() function instead of MySqlValueGenerationStrategy type

[JeffreySu]

trafficstars

Steps to reproduce

The code grenerated by Add-Migration in console:

    public partial class AddConfig : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "MuTable",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
                    //...

                }
        }
}

The issue

Sometimes while we want to just create a migration file, and store it in a CLEAN assembly, we need a CLEAN dependency to the provider.

Here are two practice:

The same setting for SqlServer is:

Id = table.Column<int>(nullable: false).Annotation("SqlServer:Identity", "1, 1"),

The same setting for SQLite is:

Id = table.Column<int>(nullable: false).Annotation("Sqlite:Autoincrement", true),

They just support String ("1, 1") and Bool (true) type, they don't have to depend to Microsoft.EntityFrameworkCore.SqlServer or Microsoft.EntityFrameworkCore.Sqlite

So if we can support a String(like "IdentityColumn") instead of an Enum type (MySqlValueGenerationStrategy.IdentityColumn) in the migration file, it will get better scalability.

Further technical details

MySQL version: 8.0 Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 3.1.2 Microsoft.AspNetCore.App version: 3.1

Other details about my project setup:

[lauxjpn]

We have planned to update the whole value generation and identity code for 5.0. We will take a look at this issue in the process.

In the meantime, you should be able to just store the underlying int value for MySqlValueGenerationStrategy.IdentityColumn with the annotation:

Id = table.Column<int>(nullable: false).Annotation("MySql:ValueGenerationStrategy", 1 /* MySqlValueGenerationStrategy.IdentityColumn */)

[JeffreySu]

Thanks @lauxjpn , when will v5.0 be released?

[mguinness]

Watch issue https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1088 for latest updates on 5.0 release.

[JeffreySu]

We have planned to update the whole value generation and identity code for 5.0. We will take a look at this issue in the process.

In the meantime, you should be able to just store the underlying int value for MySqlValueGenerationStrategy.IdentityColumn with the annotation:

Id = table.Column<int>(nullable: false).Annotation("MySql:ValueGenerationStrategy", 1 /* MySqlValueGenerationStrategy.IdentityColumn */)

It doesn't work.

[lauxjpn]

It doesn't work.

@JeffreySu Is there some sort of exception being thrown, or is the MySql:ValueGenerationStrategy just being ignored?

[JeffreySu]

It doesn't work.

@JeffreySu Is there some sort of exception being thrown, or is the MySql:ValueGenerationStrategy just being ignored?

An exception thrown, and the table was not created successfully.

[lauxjpn]

An exception thrown, and the table was not created successfully.

@JeffreySu Please share the exception with us (including its full stack trace).

[JeffreySu]

An exception thrown, and the table was not created successfully.

@JeffreySu Please share the exception with us (including its full stack trace).

It's working now, maybe the error raised by any other cases.

The code is:

migrationBuilder.CreateTable(
                name: "Accounts",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("MySql:ValueGenerationStrategy", 1),
                    ...
                } ...

[JeffreySu]

An exception thrown, and the table was not created successfully.

@JeffreySu Please share the exception with us (including its full stack trace).

It's working now, maybe the error raised by any other cases.

The code is:

migrationBuilder.CreateTable(
                name: "Accounts",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("MySql:ValueGenerationStrategy", 1),
                    ...
                } ...

@lauxjpn

When I changed MySqlValueGenerationStrategy.IdentityColumn to 1 , the project can be complied correctly, but when I try to sync migration to build the database and tables, an exception is thrown:

An unhandled exception occurred while processing the request.
MySqlException: Field 'Id' doesn't have a default value
MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask<ArraySegment<byte>> task) in ServerSession.cs, line 774
MySqlException: Field 'Id' doesn't have a default value
MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in MySqlDataReader.cs, line 130
DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
Stack 
Query 
Cookies 
Headers 
Routing
MySqlException: Field 'Id' doesn't have a default value
MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask<ArraySegment<byte>> task) in ServerSession.cs
MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in ResultSet.cs

Show raw exception details
MySqlException: Field 'Id' doesn't have a default value
MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in MySqlDataReader.cs
MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary<string, CachedProcedure> cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in MySqlDataReader.cs
MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList<IMySqlCommand> commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in CommandExecutor.cs
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
......

[lauxjpn]

When I changed MySqlValueGenerationStrategy.IdentityColumn to 1 , the project can be complied correctly, but when I try to sync migration to build the database and tables, an exception is thrown:

@JeffreySu Do you mean the exception is being thrown when you run a dotnet ef database update command?

[JeffreySu]

@lauxjpn When I run dbContext.Database.MigrateAsync()

[JeffreySu]

@lauxjpn Here is the exception output:

MySqlException: Field 'Id' doesn't have a default value
MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask<ArraySegment<byte>> task) in ServerSession.cs
MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in ResultSet.cs

Show raw exception details
MySqlConnector.MySqlException (0x80004005): Field 'Id' doesn't have a default value
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 817
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 49
MySqlException: Field 'Id' doesn't have a default value
MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in MySqlDataReader.cs
MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary<string, CachedProcedure> cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in MySqlDataReader.cs
MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList<IMySqlCommand> commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in CommandExecutor.cs
MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in MySqlCommand.cs
MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in MySqlCommand.cs
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

Show raw exception details
MySqlConnector.MySqlException (0x80004005): Field 'Id' doesn't have a default value
 ---> MySqlConnector.MySqlException (0x80004005): Field 'Id' doesn't have a default value
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 817
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 49
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 118
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 437
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 60
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 311
   at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 304
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

When I use the recommended workaround solution, The problem arises:

Id = table.Column<int>(nullable: false)
                        .Annotation("MySql:ValueGenerationStrategy", 1 /*MySqlValueGenerationStrategy.IdentityColumn*/),

The Schemas' Id colum in database, Extra infomation is blank instead of auto_increment which can be filled with the default code.

The default code is working, but I have to reference this lib all the time.

Id = table.Column<int>(nullable: false)
                        .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),

[lauxjpn]

@JeffreySu We just merged some minor code changes. These make the workaround possible. Use the latest compatible nightly build prerelease.

[JeffreySu]

We have planned to update the whole value generation and identity code for 5.0. We will take a look at this issue in the process.

In the meantime, you should be able to just store the underlying int value for MySqlValueGenerationStrategy.IdentityColumn with the annotation:

Id = table.Column<int>(nullable: false).Annotation("MySql:ValueGenerationStrategy", 1 /* MySqlValueGenerationStrategy.IdentityColumn */)

@lauxjpn I tried v5.0.0, use command:

dotnet ef migrations add TestEntites

The migration file still contains MySqlValueGenerationStrategy.IdentityColumn instead of 1.

    public partial class TestEntites : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "XncfBuilderTestEntity",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
...

[lauxjpn]

That is correct. We moved all open issues to the 6.0.0 a month ago, so that we were able to release 5.0.0 without further delay:

lauxjpn modified the milestones: 5.0.0, 6.0.0 on Apr 28

So expect this feature to be added for 6.0.0.

You should still be able to manually replace MySqlValueGenerationStrategy.IdentityColumn with 1 (or just run a PowerShell script that does it after you added a migration) until then.

(Though it would be possible to introduce an opt-in option for 5.0.x, that would output enum values as their underlying type's value, if enabled.)

[JeffreySu]

So that means I have to wait the 6.0 production version. I could not publish a production release Nuget package with a preview reference.

When will proecution 6.0 be released?

[lauxjpn]

When will proecution 6.0 be released?

With EF Core 6 in November.

I could not publish a production release Nuget package with a preview reference.

What project/package is this currently blocking?

---

Here is a workaround you can use, in case just replacing the enum value with the value of the underlying type manually or via script is not an option for you:

Program.cs

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Design.Internal;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Design.Internal;

[assembly: DesignTimeProviderServices("IssueConsoleTemplate.CustomMySqlDesignTimeServices")]

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
    }

    public class CustomCSharpHelper : CSharpHelper
    {
        public CustomCSharpHelper(IRelationalTypeMappingSource relationalTypeMappingSource)
            : base(relationalTypeMappingSource)
        {
        }

        public override string Literal(Enum value)
        {
            var type = value.GetType();
            var enumValue = base.Literal(value);
            
            return type == typeof(MySqlValueGenerationStrategy) ||
                   type == typeof(DelegationModes)
                ? (string) Literal((dynamic) Convert.ChangeType(value, Enum.GetUnderlyingType(type))) +
                  $" /* {enumValue} */"
                : enumValue;
        }
    }

    // You should have been able to override MySqlDesignTimeServices just like below, but we forgot to mark
    // ConfigureDesignTimeServices as virtual.
    //
    // public class CustomMySqlDesignTimeServices : MySqlDesignTimeServices
    // {
    //     public override void ConfigureDesignTimeServices(IServiceCollection serviceCollection)
    //     {
    //         base.ConfigureDesignTimeServices(serviceCollection);
    //
    //         serviceCollection.AddSingleton<ICSharpHelper, CustomCSharpHelper>();
    //     }
    // }
    //
    // Therefore, you need to use a workaround here (composition/delegation, instead of inheritance):
    public class CustomMySqlDesignTimeServices : IDesignTimeServices
    {
        private readonly MySqlDesignTimeServices _mySqlDesignTimeServices;

        public CustomMySqlDesignTimeServices()
        {
            _mySqlDesignTimeServices = new MySqlDesignTimeServices();
        }

        public virtual void ConfigureDesignTimeServices(IServiceCollection serviceCollection)
        {
            _mySqlDesignTimeServices.ConfigureDesignTimeServices(serviceCollection);

            serviceCollection.AddSingleton<ICSharpHelper, CustomCSharpHelper>();
        }
    }

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

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1205_01";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder.UseMySql(connectionString, serverVersion)
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        configure => configure
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    internal static class Program
    {
        private static void Main(string[] args)
        {
        }
    }
}

You have to do the following:

• Use the DesignTimeProviderServices assembly attribute, to mark your custom class that registers the design time services.
• Register your CSharpHelper derived class with it, that then overrides the enum related Literal() method.
• Let your overridden method return the value of the underlying type, when applicable.

[JeffreySu]

@lauxjpn

If I modify the enum value to String "1" or Int32 1, when I run the migration, the exception will be thrown:

MySqlException: Field 'Id' doesn't have a default value
MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask<ArraySegment<byte>> task) in ServerSession.cs, line 816

MySqlException: Field 'Id' doesn't have a default value
MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in MySqlDataReader.cs, line 133

DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

Stack:

[JeffreySu]

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/1329#issuecomment-860780498

[JeffreySu]

I've searched the source code, there is only one place to set the AUTO_INCREMENT:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/ebe011a6f1b2a2a9709fe558cfc7ed3215b55c37/src/EFCore.MySql/Migrations/MySqlMigrationsSqlGenerator.cs#L1234

So that means only MySqlValueGenerationStrategy.IdentityColumn can lead the column be set to AUTO_INCREMENT. So only change the value to 1 is not a perfect solution in current state.

[lauxjpn]

So that means only MySqlValueGenerationStrategy.IdentityColumn can lead the column be set to AUTO_INCREMENT. So only change the value to 1 is not a perfect solution in current state.

That is not correct. The code looks like the following:

var valueGenerationStrategy = MySqlValueGenerationStrategyCompatibility.GetValueGenerationStrategy(
    operation.GetAnnotations()
        .OfType<Annotation>()
        .ToArray());

var autoIncrement = false;

if (valueGenerationStrategy == MySqlValueGenerationStrategy.IdentityColumn &&
    string.IsNullOrWhiteSpace(operation.DefaultValueSql) && operation.DefaultValue == null)
{
    switch (matchType)
    {
        case "tinyint":
        case "smallint":
        case "mediumint":
        case "int":
        case "bigint":
            autoIncrement = true;
            break;
        // ...
}

// ...

if (autoIncrement)
{
    builder.Append(" AUTO_INCREMENT");
}

So it is the MySqlValueGenerationStrategyCompatibility.GetValueGenerationStrategy() call, that is controlling whether AUTO_INCREMENT is emitted or not.

And MySqlValueGenerationStrategyCompatibility.GetValueGenerationStrategy() looks like this (in 6.0/5.0, but 3.2.5 is similar):

public static MySqlValueGenerationStrategy? GetValueGenerationStrategy(IAnnotation[] annotations)
{
    var valueGenerationStrategy = ObjectToEnumConverter.GetEnumValue<MySqlValueGenerationStrategy>(
        annotations.FirstOrDefault(a => a.Name == MySqlAnnotationNames.ValueGenerationStrategy)?.Value);

    // ...
}

The ObjectToEnumConverter.GetEnumValue<>() call ensures, that an underlying value (usually int) is translated into the real enum value. Therefore, after the GetValueGenerationStrategy() call, the code can safely work with enum values.

For further discussion, see https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/1329#issuecomment-861034985, where I posted some code to demonstrate, that the code does work as expected when I execute it.

[dide0100]

@lauxjpn will this feature be available in release 8? I’ve been following the issue and it seems it’s been pushed forward a couple of times. Best regards

[lauxjpn]

@dide0100 Unfortunately not, but it will be part of the 9.0.0 release. We did the preliminary work to resolve this issue with https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/1852/commits/8e4ba4b01c8864eca3b5ced7f51297e01d32b361.