I am using EF Code First and MySQL via Pomelo. I need an auto-increment field other than the key. Purpose of this field is to show human readble values to end-user other than uuid and guids. The table id/key is uuid anyway. Also my definition is Fluent API. By using following definition, the generated table does not have any field as auto increment:

public class Car
{
    public Guid CarId { get; set; }
    public string CarName { set; get; }
    public int CarNumber { set; get; }
}

public class CarTypeConfiguration : IEntityTypeConfiguration<Car>
{
    public void Configure(EntityTypeBuilder<Car> builder)
    {
        builder.HasKey(x => x.CarId);
        builder.Property(x => x.CarNumber).ValueGeneratedOnAdd();
    }
}

The purpose is that the field CarNumber be automatically incremented by inserting each new record which is not happening.

No matter if I am using ValueGeneratedOnAdd() or UseMySqlIdentityColumn(), the generated migration and table script is as follow:

//20220217153551_AddCarNumber
migrationBuilder.AddColumn<int>(
    name: "CarNumber",
    table: "car",
    type: "int",
    nullable: false,
    defaultValue: 0)
    .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

//20220217153551_AddCarNumber.Designer.cs
modelBuilder.Entity("MyNameSpace.Car", b =>
    {
        b.Property<Guid>("CarId")
            .ValueGeneratedOnAdd()
            .HasColumnType("char(36)");

        b.Property<int>("CarNumber")
            .ValueGeneratedOnAdd()
            .HasColumnType("int");

        b.HasKey("CarId");

        b.ToTable("Car");
    });

And the exported SQL script from phpMyAdmin

CREATE TABLE `Car` (
  `CarId` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `CarNumber` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `Car`
  ADD PRIMARY KEY (`CarId`);

By all my tries the value of the CarNumber remained zero and not increased on any of the inserted records.

I also made CarNumber an index by adding builder.HasIndex(x => x.CarNumber) to see if resolves the error or not, but no chance.

My environment is .Net 5, Microsoft.EntityFrameworkCore 5.0.11, Pomelo.EntityFrameworkCore.MySql 5.0.2.

Update 1

Being recommended here, added builder.HasAlternateKey(x => x.CarNumber); but did not work. It did not add AUTO_INCREMENT to the generated table.

Update 2

Changing .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn) to .Annotation("MySql:ValueGeneratedOnAdd", true) did not change anything. Suggested here.

Update 3

Convinced to use customized migration builder as:

migrationBuilder.Sql("ALTER TABLE `Car` ADD `CarNumber` int AUTO_INCREMENT UNIQUE;");

And the problem fixed as a work-around.