2

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.

Afshar Mohebi
  • 10,479
  • 17
  • 82
  • 126
  • A GUID isn't a number and can't be incremented, much less auto-incremented. Autoincrementing means the database itself will increase the numeric ID value every time a new item is inserted. If you want to use a GUID primary key (not a good idea) you can only *assign* a new GUID value to the column. In the database, this means specifying a default value. On the client side, this means assigning a new GUID value to `CarId` – Panagiotis Kanavos Feb 17 '22 at 15:24
  • What is the *actual* code you used? `IdentityColumn` explicitly specifies an AUTO_INCREMENT numeric column. You can't have an AUTO_INCREMENT uuid column in MySQL or any database – Panagiotis Kanavos Feb 17 '22 at 15:30
  • @PanagiotisKanavos sorry, I was updating my question by adding codes and scripts. My desired auto identity field is of type number (int). – Afshar Mohebi Feb 17 '22 at 15:33
  • CarId is the key/primary and is not intended to be auto increment. And is guid as a general rule of the project. – Afshar Mohebi Feb 17 '22 at 15:36
  • What you describe is an edge case - in most cases, the AUTO_INCREMENT column is the Primary Key as well. [There's a known issue with Pomelo](https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/711) which fails when the AUTO_INCREMENT property isn't a key *on existing tables* - the generated Default Value will set all existing row values to 0. If you explicitly add the column with a SQL script, eg `ALTER TABLE "Area" ADD "AutoId" int AUTO_INCREMENT UNIQUE` it will work – Panagiotis Kanavos Feb 17 '22 at 16:01
  • @PanagiotisKanavos So are you remmending to edit Migration-Up and add the `ALTER TABLE ...` there? Anyway there is not sign of `AUTO_INCREMENT` in the generated SQL script. – Afshar Mohebi Feb 17 '22 at 16:03
  • 2
    As for `And is guid as a general rule of the project` that's unfortunate. Project rules won't change the fact that a GUID generates essentially random values that force insertions in the middle of any data page in a table, resulting in fragmentation. They're useless for range searches too. Plus they take a lot more space. – Panagiotis Kanavos Feb 17 '22 at 16:08
  • The fragmentation problem can be solved by using a *sequential* GUID algorithm, which is guaranteed to produce always-increasing values. You should consider using `[SequentialGuidValueGenerator](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.valuegeneration.sequentialguidvaluegenerator?view=efcore-6.0) with [HasValueGenerator](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.builders.propertybuilder-1.hasvaluegenerator?view=efcore-6.0) – Panagiotis Kanavos Feb 17 '22 at 16:08
  • 1
    `you are recommending` I'm not recommending anything, except changing the way the GUID is generated. I linked to a known issue with Pomelo. That linked issue contains several workarounds, including manually generating the migrations and perhaps the easiest, just writing the SQL statement directly instead of trying to get EF Core/Pomelo to generate it correctly. Personally, I always design the database separately. The same database is often used by *multiple* applications, and the shape one application wants isn't compatible with others – Panagiotis Kanavos Feb 17 '22 at 16:10
  • @PanagiotisKanavos convinced to use the customized migration builder you mentioned above. Found not a better way. See Update 3. Please provide this as an answer so I can accept it. – Afshar Mohebi Feb 17 '22 at 18:36

0 Answers0