How to create Autoincrement column in SQLite using EF core?

Zubair Rana picture Zubair Rana · Mar 31, 2018 · Viewed 13.4k times · Source

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.

Answer

Ivan Stoev picture Ivan Stoev · Apr 1, 2018

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.