How to avoid the AUTOINCREMENT keyword with EFC and SQLite?

.net .net-core c# entity-framework-core sqlite

Question

AUTOINCREMENT doesn't usually need to be used in SQLite. Even without this keyword, automatically generated IDs work.

However, integer primary key is declared as AUTOINCREMENT when using Entity Framework Core (as of 2.1.3) and SQLite. Is there a way to avoid this?

I tried adding [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute to the entity's primary key property, but this disables automatic key generation altogether and I have to set it manually for each insert. Otherwise, EFC tries to insert with an explicit Id = 0.

So I need it to be treated as database generated on insert, I'd just like to avoid the unnecessary AUTOINCREMENT keyword. Is there a way to do this?

Edit

Here's some example C# code:

using System;
using Microsoft.EntityFrameworkCore;

namespace Experiments
{
    public class Entity
    {
        public long Id { get; set; }

        public string Text { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<Entity> Entities { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=temp.sqlite");
        }
    }

    public static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        private static void Main()
        {
            using (var context = new Context())
            {
                context.Database.EnsureCreated();
            }
        }
    }
}

This will create a database that looks like this, when viewed with DB Browser for SQLite:

enter image description here

I'd rather not have the primary key declared as AUTOINCREMENT, which also creates the sqlite_sequence table. SQLite can generate keys without that keyword, and it does it simpler and faster.

1
1
9/24/2018 6:51:49 PM

Accepted Answer

As far as I can tell, the issue is caused by the following code in the current SqliteMigrationsAnnotationProvider class:

if (property.ValueGenerated == ValueGenerated.OnAdd
    && property.ClrType.UnwrapNullableType().IsInteger()
    && !HasConverter(property))
{

    yield return new Annotation(SqliteAnnotationNames.Autoincrement, true);
}

which is forcing the SqliteMigrationsSqlGenerator class to include AUTOINCREMENT.

I don't know why is that, I would assume it's a leftover bug, but you'd better ask in their issue tracker.

Looking at the code, looks like setting fake value converter will prevent that and can be used as temporary workaround:

modelBuilder.Entity<Entity>()
    .Property(e => e.Id)
    .HasConversion(v => v, v => v);
2
9/24/2018 6:51:31 PM

Popular Answer

Defined the model class as below. So you will not map the Id column and use another column as PK.

[NotMapped]
public override int Id
{
    get
    {
        return PKColumn;
    }
    set
    {
        PKColumn = value;
    }
}

[Key]
public virtual int PKColumn { get; set; }


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