How to create Autoincrement column in SQLite using EF core?

.net-standard-2.0 auto-increment c# entity-framework-core sqlite

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.

1
15
3/31/2018 8:49:19 PM

Accepted Answer

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.

19
4/1/2018 2:20:42 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow