How can set AUTO_INCREMENT initial value for primary key?

[Satancito]

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

like this https://stackoverflow.com/questions/1485668/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

[lauxjpn]

There is currently no official way yet for Pomelo to set an initial auto increment value. However, this feature will be added for 6.0.0.

In the meantime, I can provide a temporary workaround if you need one.

[Satancito]

My short solution for now is to add manually the following line in migration for each entity.

migrationBuilder.Sql("ALTER TABLE `Identity.User` AUTO_INCREMENT = 10000;");

But this is not automatic as in the case of the command dotnet ef migrations add.

If it is not much complexity that this is added in version 5.0.0 I would be grateful.

Adding additional annotation support for method HasAnnotation in PropertyBuilder, TableBuilder(Migration class) or EntityTypeBuilder. It would be a great help.

e.g.

propertyBuilderInstance.Annotation("MySql:AutoIncrement", "10000");
tableBuilderInstance.Annotation("MySql:AutoIncrement", "10000");
EntityTypeBuilderInstance.Annotation("MySql:AutoIncrement", "10000");

[lauxjpn]

If it is not much complexity that this is added in version 5.0.0 I would be grateful.

Unfortunately, we will not add this to 5.0, because it is too invasive.

We could consider backporting the recent table options support (#1463), because we can add it without any side effects. You would then be able to call entity.HasTableOption("AUTO_INCREMENT", "10000"). When you upgrade to 6.0, you would need to replace it with the new proper way we will introduce (similar to the other providers).

On the other hand, since you only have to add your migrationBuilder.Sql() script once for every table that needs a special auto increment seed (and only for a single migration), it might be good enough for you to do this once and just wait for the 6.0 implementation (we will likely implement it for 6.0.0-preview.6).

(If you definitely want to use a annotation for it though, I can post you some code, so you can implement it as a custom annotation that only works for you.)

[Satancito]

Thanks. I need the example.

[lauxjpn]

@Satancito Here is the code:

Program.cs

using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
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;

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

    //
    // Custom service implementations:
    //
    
    public static class CustomMySqlAnnotationNames
    {
        public const string Prefix = "CustomMySql:";
        public const string AutoIncrement = Prefix + "AutoIncrement";
    }

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

        public override IEnumerable<IAnnotation> For(ITable table)
        {
            var annotations = base.For(table);
            var entityType = table.EntityTypeMappings.First().EntityType;
            
            var autoIncrement = entityType.FindAnnotation(CustomMySqlAnnotationNames.AutoIncrement);
            if (autoIncrement is not null)
            {
                annotations = annotations.Append(autoIncrement);
            }

            return annotations;
        }
    }

    public class CustomMySqlMigrationsSqlGenerator : MySqlMigrationsSqlGenerator
    {
        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);
            
            if (operation[CustomMySqlAnnotationNames.AutoIncrement] is int autoIncrement) // or long
            {
                builder
                    .Append(" AUTO_INCREMENT ")
                    .Append(autoIncrement.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);

            if (operation[CustomMySqlAnnotationNames.AutoIncrement] is int autoIncrement) // or long
            {
                builder.Append("ALTER TABLE ")
                    .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
                    .Append(" AUTO_INCREMENT ")
                    .Append(autoIncrement.ToString());

                builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
                EndStatement(builder);
            }
        }
    }
    
    //
    // DbContext:
    //
    
    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        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(CustomMySqlAnnotationNames.AutoIncrement, 10_000);
                });
        }
    }

    internal 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);
        }
    }
}

It generates the following SQL when run:

Generated SQL

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (52ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      DROP DATABASE `Issue1460`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE DATABASE `Issue1460`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      ALTER DATABASE CHARACTER SET utf8mb4;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (52ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      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;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (18ms) [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: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT `i`.`IceCreamId`, `i`.`Name`
      FROM `IceCreams` AS `i`
      LIMIT 2

It should work with migrations as well.

[Satancito]

Nice. Elegant solution. Thanks a lot @lauxjpn .

[cguijt]

There is currently no official way yet for Pomelo to set an initial auto increment value. However, this feature will be added for 6.0.0.

In the meantime, I can provide a temporary workaround if you need one.

I came across this comment and I was wondering of this was indeed released in version 6, because I can't seem to find any implementation for it.

[lauxjpn]

@cguijt No, we did not implement this yet. Let's reopen this issue, so that we don't forget about it for 7.0.

[nikhil777jais]

Hi @lauxjpn, I am using the 7.0.0 version of EntityFrameworkCore.MySql but seems it is still not implemented, I will be very thankful If I get any update on that.