How can set AUTO_INCREMENT initial value for primary key using Pomelo.EntityFrameworkCore.MySql?

Question

How can set AUTO_INCREMENT initial value for primary key using Pomelo.EntityFrameworkCore.MySql?.

like this How to set initial value and auto increment in MySQL?

CREATE TABLE my_table (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) AUTO_INCREMENT = 10000;

The issue

I need to create a table with bigint primary key starting at 10000.

Generated script

CREATE TABLE `Identity.User` (
    `Id` bigint NOT NULL AUTO_INCREMENT,
    `UniqueId` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
    `Username` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
    `NormalizedUsername` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
    `Password` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
    `Email` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
    `NormalizedEmail` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
    `Phone` varchar(16) CHARACTER SET utf8mb4 NULL,
    `Mobile` varchar(16) CHARACTER SET utf8mb4 NOT NULL,
    `CreatedAt` datetime(6) NOT NULL,
    `Enabled` tinyint(1) NOT NULL,
    `Active` tinyint(1) NOT NULL,
    `EmailConfirmed` tinyint(1) NOT NULL,
    `EmailConfirmationCode` longtext CHARACTER SET utf8mb4 NOT NULL,
    `EmailConfirmationDeadline` datetime(6) NOT NULL,
    `MobileConfirmed` tinyint(1) NOT NULL,
    `MobileConfirmationCode` longtext CHARACTER SET utf8mb4 NOT NULL,
    `MobileConfirmationDeadline` datetime(6) NOT NULL,
    `LoginFailCount` int NOT NULL,
    `LockoutUntil` datetime(6) NOT NULL,
    CONSTRAINT `P_Identity.User__Id` PRIMARY KEY (`Id`)
) CHARACTER SET utf8mb4; -- **AUTO_INCREMENT=10000 need this**

My C# static method for Identity column

public static PropertyBuilder<long> SetIdentity(this PropertyBuilder<long> builder, DatabaseFacade database, int startsAt = 1, int incrementsBy = 1)
{
    switch (database)
    {
        case DatabaseFacade db when db.IsSqlServer():
            SqlServerPropertyBuilderExtensions.UseIdentityColumn(builder, startsAt, incrementsBy);
            break;
        case DatabaseFacade db when db.IsNpgsql():
            NpgsqlPropertyBuilderExtensions.HasIdentityOptions(builder, startsAt, incrementsBy);
            break;
        case DatabaseFacade db when db.IsMySql():
            //MySqlPropertyBuilderExtensions;
            break;
        case DatabaseFacade db when db.IsOracle():
            OraclePropertyBuilderExtensions.UseIdentityColumn(builder, startsAt, incrementsBy);
            break;
        default:
            throw new NotImplementedException("Unknown database provider");
    }
    builder.ValueGeneratedOnAdd();
    return builder;
}

Further technical details

MySQL version: 8.x.x Operating system: Windows Pomelo.EntityFrameworkCore.MySql version: 5.0.0 Microsoft.AspNetCore.App version: 5.0.0

Answer 1

Thanks to @lauxjpn(lauxjpn)

Original code in How can set AUTO_INCREMENT initial value for primary key?

My modified solution.

Program.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure.Internal;
using Pomelo.EntityFrameworkCore.MySql.Metadata.Internal;
using Pomelo.EntityFrameworkCore.MySql.Migrations;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Satancito
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "<Pending>")]
    public class CustomMySqlAnnotationProvider : MySqlAnnotationProvider
    {
        public const string AutoincrementAnnotation = "Insane:AutoIncrement";

        public CustomMySqlAnnotationProvider(
            RelationalAnnotationProviderDependencies dependencies,
            IMySqlOptions options)
            : base(dependencies, options)
        {
        }

        public override IEnumerable<IAnnotation> For(ITable table)
        {
            var annotations = base.For(table);
            IEntityType entityType = table.EntityTypeMappings.First().EntityType;

            IAnnotation autoIncrement = entityType.FindAnnotation(AutoincrementAnnotation);
            if (autoIncrement is not null)
            {
                annotations = annotations.Append(autoIncrement);
            }

            return annotations;
        }

    }

    public class CustomMySqlMigrationsSqlGenerator : MySqlMigrationsSqlGenerator
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "<Pending>")]
        public CustomMySqlMigrationsSqlGenerator(
            MigrationsSqlGeneratorDependencies dependencies,
            IRelationalAnnotationProvider annotationProvider,
            IMySqlOptions options)
            : base(dependencies, annotationProvider, options)
        {
        }


        protected override void Generate(
            CreateTableOperation operation,
            IModel model,
            MigrationCommandListBuilder builder,
            bool terminate = true)
        {
            base.Generate(operation, model, builder, terminate: false);
            var autoIncrementValue = operation[CustomMySqlAnnotationProvider.AutoincrementAnnotation];
            if (autoIncrementValue is not null && (autoIncrementValue.GetType().Equals(typeof(int)) || autoIncrementValue.GetType().Equals(typeof(long))))//Check if annotation exists.
            {
                builder.Append($" AUTO_INCREMENT {autoIncrementValue.ToString()}");
            }

            if (terminate)
            {
                builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
                EndStatement(builder);
            }
        }

        protected override void Generate(
            AlterTableOperation operation,
            IModel model,
            MigrationCommandListBuilder builder)
        {
            base.Generate(operation, model, builder);
            var autoIncrementValue = operation[CustomMySqlAnnotationProvider.AutoincrementAnnotation];

            if (autoIncrementValue is not null && (autoIncrementValue.GetType().Equals(typeof(int)) | autoIncrementValue.GetType().Equals(typeof(long))))
            {
                builder.Append("ALTER TABLE ")
                    .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
                    .Append(" AUTO_INCREMENT ")
                    .Append(autoIncrementValue.ToString());

                builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
                EndStatement(builder);
            }
        }
    }

    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; } = null!;
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; } = null!; //Using NRT

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                // Register our custom service implementations (and some logging).
                var serviceProvider = new ServiceCollection()
                    .AddEntityFrameworkMySql()
                    .AddSingleton<IRelationalAnnotationProvider, CustomMySqlAnnotationProvider>()
                    .AddScoped<IMigrationsSqlGenerator, CustomMySqlMigrationsSqlGenerator>()
                    .AddScoped(
                        _ => LoggerFactory.Create(
                            b => b
                                .AddConsole()
                                .AddFilter(level => level >= LogLevel.Information)))
                    .BuildServiceProvider();

                var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1460";
                var serverVersion = ServerVersion.AutoDetect(connectionString);

                optionsBuilder.UseMySql(connectionString, serverVersion)
                    .UseInternalServiceProvider(serviceProvider) // <-- use our service provider 
                    .EnableSensitiveDataLogging()
                    .EnableDetailedErrors();
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    // Add the custom annotation.
                    entity.HasAnnotation(CustomMySqlAnnotationProvider.AutoincrementAnnotation, 10_000);
                });
        }
    }

    public static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(
                new IceCream { Name = "Vanilla" });

            context.SaveChanges();

            var iceCream = context.IceCreams.Single();

            Trace.Assert(iceCream.IceCreamId == 10_000);
        }
    }
}

Sql generated when create table

CREATE TABLE `IceCreams` (
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      ) CHARACTER SET utf8mb4 AUTO_INCREMENT 10000;