c# - How to avoid the AUTOINCREMENT keyword with EFC and 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:

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.

标签： c#.netsqlite.net-coreentity-framework-core

Answer 1

据我所知，问题是由当前SqliteMigrationsAnnotationProvider类中的以下代码引起的：

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

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

这迫使SqliteMigrationsSqlGenerator班级包括AUTOINCREMENT.

我不知道为什么会这样，我认为这是一个遗留错误，但你最好在他们的问题跟踪器中询问。

查看代码，看起来设置假值转换器可以防止这种情况发生，并且可以用作临时解决方法：

modelBuilder.Entity<Entity>()
    .Property(e => e.Id)
    .HasConversion(v => v, v => v);