CODEWITHSUNDEEP
c#.net-coreentity-framework-coremariadbauto-increment
ldu
ldu

Reputation: 13

Generate auto-increment int column in MariaDB with EFCore

The Problem:

I'm trying to create a table with efcore in .netcore 2.2 using codefirst having an auto-increment integer primary key starting at 0. All solutions i found result in either an error on adding migration or the auto-incrementing works but start at int.MinValue.

All the following solutions have been tried seperated, not combined:


Solution 1:

Do nothing. Just let efcore do its default thing.

-> The column auto-increments but starts at int.MinValue


Solution 2:

setting annotation "[DatabaseGenerated(DatabaseGeneratedOption.Identity)]" on primary key.

-> The column auto-increments but starts at int.MinValue


Solution 3:

modelBuilder.Entity<User>()
    .Property(u => u.Id)
    .ValueGeneratedOnAdd();

-> The column auto-increments but starts at int.MinValue


Solution 4:

modelBuilder.HasSequence<int>("User_seq")
    .StartsAt(0)
    .IncrementsBy(1);

modelBuilder.Entity<User>()
    .Property(u => u.Id)
    .HasDefaultValueSql("NEXT VALUE FOR User_seq");

-> Adding a migration fails with an error: 

    "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SEQUENCE User_seq START WITH 0 INCREMENT BY 1' at line 1"




Solution 5:

changing the datatype of Id to uint.

-> The column auto-increments but starts at int.MaxValue





My User entity:


    public class User
    {
        public int Id { get; set; }
        public string FirstName{ get; set; }
        public string LastName { get; set; }
    }




Provider used: Pomelo.EntityFrameworkCore.MySql


To clarify my question: How do I create an auto-incrementing integer primary key column starting at 0 incrementing by 1 in MariaDB using efcore codefirst?

Upvotes: 1

Views: 2168

Answers (1)

Rick James
Rick James

Reputation: 142518

You cannot.

MariaDB (MySQL) treats 0 as meaning to create a new AUTO_INCREMENT value.

Do not assume properties of AUTO_INCREMENT that it does not provide. It only says that each new value will be different than the existing values.

Probably int.MinValue is some big negative value (-2^31).

Upvotes: 1

Related Questions