SQLite: Make AUTOINCREMENT more first-class

[smitpatel]

public partial class Preference
{
    public int PreferenceId { get; set; }
    public string Name { get; set; }
    public int? Value { get; set; }
    public string ValueString { get; set; }
}
modelBuilder.Entity<Preference>(entity =>
{
    entity.HasKey(e => e.PreferenceId);

    entity.Property(e => e.PreferenceId).HasColumnName("PreferenceID");

    entity.Property(e => e.Name).HasMaxLength(50);

    entity.Property(e => e.Value).HasDefaultValueSql(@"((0))");

    entity.Property(e => e.ValueString)
        .HasMaxLength(50)
        .HasDefaultValueSql(@"('')");
});

Generates following migration

migrationBuilder.CreateTable(
    name: "Preference",
    columns: table => new
    {
        PreferenceID = table.Column<int>(nullable: false)
            .Annotation("Sqlite:Autoincrement", true),
        Name = table.Column<string>(maxLength: 50, nullable: true),
        Value = table.Column<int>(nullable: true, defaultValueSql: "((0))")
            .Annotation("Sqlite:Autoincrement", true),
        ValueString = table.Column<string>(maxLength: 50, nullable: true, defaultValueSql: "('')")
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Preference", x => x.PreferenceID);
    });

This is because of ad-hoc logic here
https://github.com/aspnet/EntityFrameworkCore/blob/b86eb8548a0deedc1199c3b4bc6b8632bd7824e3/src/EFCore.Sqlite.Core/Migrations/Internal/SqliteMigrationsAnnotationProvider.cs#L34-L38

And due to other hacks, later all annotations which are not on PK gets ignored. We should make autoincrement a first class for provider just like how SqlServer deals with identity.

@ErikEJ - SqlCE faces the same issue due to similar code and in SQL CE it tries to create multiple Identity columns failing at Update-Database command. You would also need to update SQL CE provider. (I found this after talking to customer on slack who hit issue on SQL CE)

[ajcvickers]

Assigning to @AndriySvyryd to give SQLite the same treatment (flag conventions, etc.) that SQL Server uses for this. @bricelam can help out on the Migrations part, if needed.

[ajcvickers]

@AndriySvyryd I added "propose-close" to make you feel at home with this one.