How to create Autoincrement column in SQLite using EF core?

Question

I am using Entity Framework Core 2.0 for Sqlite code first in my UWP and .NET Standard app. My model has an entity of type Primary Key integer which should be served as auto increment according to SQLite documentation. But in real for every row that Identity column is getting 0 as a value. Please help because i found no helping material related to this issue.

This is my property without any data annotaion.

public Int32 No { get; set; }

I have used fluent API

modelBuilder.Entity<TurnosGeneral>()
            .HasKey(c => new { c.No, c.Cod_Turno });

And value is inserted here

db.TurnosGenerals.Add(new TurnosGeneral { Cod_Turno = numeroTurnoTextBlock.Text });

db.SaveChanges();

For every row inserted c.No is 0.

Solution 1

My model has an entity of type Primary Key integer which should be served as auto increment

The problem is that the property in question is not a PK, but a part of a composite PK, in which case it's not considered as auto generated by convention, as explained in the Generated Values Conventions section of the EF Core documentation:

By convention, non-composite primary keys of type short, int, long, or Guid will be setup to have values generated on add. All other properties will be setup with no value generation.

You need to specify that explicitly:

modelBuilder.Entity<TurnosGeneral>()
    .Property(e => e.No)
    .ValueGeneratedOnAdd();

Update: The above is the general approach applicable for most of the databases. But SQLite supports AutoIncrement only for column of type INTEGER PRIMARY KEY, hence this is not EF Core limitation. Either don't use auto increment or make it non-composite PK.

Solution 2

I just ran into this with a SQLite in memory database I was using for testing. In my case I had a business class with a primary key field called ContactCategoryId:

public class ContactCategory
{
    [Required]
    [Display(Name = "Contact category ID")]
    public int ContactCategoryId { get; set; }

And I was also using the fluent approach for the settings:

public void Configure(EntityTypeBuilder<ContactCategory> modelBuilder)
{
    modelBuilder.ToTable("CONTACT_CATEGORY", _schema);

    modelBuilder.HasKey(x => x.ContactCategoryId);

    modelBuilder.Property(x => x.ContactCategoryId)
        .HasColumnName(@"ContactCategoryID")
        //.HasColumnType("int") Weirdly this was upsetting SQLite
        .IsRequired()
        .ValueGeneratedOnAdd()
        ;

Commenting out the line for .HasColumnType("int") fixed the error for me.

Solution 3

Looks like a bug. Check this : https://github.com/aspnet/EntityFrameworkCore/issues/11961

My workaround: Manually change : .ValueGeneratedNever() to .ValueGeneratedOnAdd() on Id columns in my DBContext class.

Comments

[Zubair Rana]

I have already tried this and always got an error of " SqliteException: SQLite Error 19: 'NOT NULL constraint failed: " Which means instead of auto generation its expecting user generated values on Add. No doubt this works good with sql server in EF 6.

[Ivan Stoev]

It's working good with SqlServer in EF Core 2.0.2 as well. Can't find SQLite specific requirement in documentation.

[Ivan Stoev]

Ok, I've installed [SQLite EF Core Database Provider)(docs.microsoft.com/en-us/ef/core/providers/sqlite) and got this migration (note Sqlite:Autoincrement): migrationBuilder.CreateTable(name: "TurnosGeneral", columns: table => new { No = table.Column<int>(nullable: false).Annotation("Sqlite:Autoincrement", true), Cod_Turno = table.Column<string>(nullable: false) }, constraints: table => { table.PrimaryKey("PK_TurnosGeneral", x => new { x.No, x.Cod_Turno }); });

[Ivan Stoev]

But you are right, there seem to be a bug in SQLite provider migration generator which doesn't take that annotation into account. Consider posting an issue in their issue tracker.

[Zubair Rana]

Annotation("Sqlite:Autoincrement", true) is also not working already tried. BTW thanks for your comments. It seems i have to create new issue on github for EF Core for SQLite.

[Ivan Stoev]

You are welcome. I've just looked at their SqliteMigrationsSqlGenerator code, it appends "AUTOINCREMENT" only for columns which are non-composite PK :(

[Ivan Stoev]

But looks like this is actually SQLite requirement

[Zubair Rana]

Yes its SQLite requirement and now SQLite is officially maintained by Microsoft.

[Ivan Stoev]

The point is, if database does not support it, there is nothing EF Core can do. So we :)

[Vedran Mandić]

I can confirm the behavior is still the same, i.e. I do not know how to surpass this with sqlite, i.e. generate an id integer which is not PK

[Alex Albu]

For me it worked only after I completely removed entity.Property(e => e.Id).HasColumnName("ID");