Can't create int-autoincrement primary key on sqlite using EF Core-CodePudding

I'm trying to create a simple table where the primary key is an autoincrementing integer. So what I did is, create that .NET class and tried to create the migration.

this is the class-entity:

public class PasswordEntry : IEntity<int>
{
    [Key]
    [Column("Id", Order = 0, TypeName = DatabaseType.INT)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column("Title", Order = 1, TypeName = "nvarchar2(300)")]
    [Required(AllowEmptyStrings = false), MaxLength(300)]
    public string Title { get; set; } = default!;

    [Column("UserName", Order = 2, TypeName = "nvarchar2(2000)")]
    [MaxLength(2000)]
    public string? UserName { get; set; }

    [Column("Password", Order = 3, TypeName = "nvarchar2(4000)")]
    [MaxLength(4000)]
    public string? PasswordHash { get; set; }

    [Column("Url", Order = 4, TypeName = "nvarchar2(5000)")]
    [MaxLength(5000)]
    public string? Url { get; set; }
}

and this is the context:

internal sealed class DatabaseContext : DbContext
{
    public DatabaseContext(string databasePath)
    {
        _dbPath = databasePath;
    }
    
    private readonly string _dbPath;

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite($"Data Source={_dbPath}");

    
    public DbSet<PasswordEntry> PasswordEntries { get; set; } = default!;
}

the generated migrations Up-method is:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "PasswordEntries",
        columns: table => new
        {
            Id = table.Column<int>(type: "int", nullable: false)
                .Annotation("Sqlite:Autoincrement", true),
            Title = table.Column<string>(type: "nvarchar2(300)", maxLength: 300, nullable: false),
            UserName = table.Column<string>(type: "nvarchar2(2000)", maxLength: 2000, nullable: true),
            Password = table.Column<string>(type: "nvarchar2(4000)", maxLength: 4000, nullable: true),
            Url = table.Column<string>(type: "nvarchar2(5000)", maxLength: 5000, nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_PasswordEntries", x => x.Id);
        });
}

and when I try to update the database I get the error-message:

Applying migration '20220407093200_AddedPasswordEntryTable'.
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "PasswordEntries" (
    "Id" int NOT NULL CONSTRAINT "PK_PasswordEntries" PRIMARY KEY AUTOINCREMENT,
    "Title" nvarchar2(300) NOT NULL,
    "UserName" nvarchar2(2000) NULL,
    "Password" nvarchar2(4000) NULL,
    "Url" nvarchar2(5000) NULL
);
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer) MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer) MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
SQLite Error 1: 'AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY'.

but the migrations and the update both show that the Id-Column is an integer... any idea what could be the problem here?

CodePudding user response：

Error message:

'AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY' but your type is "int"

the correct sql should be like this, (not int)

Id = table.Column<integer>(type: "int", nullable: false)
                .Annotation("Sqlite:Autoincrement", true),AUTOINCREMENT,

You dont need type definition in Entity Id. Ef core will automatically recognize that it is key, "Integer" and Required.

public int Id { get; set; }

CodePudding user response：

I think the problem is [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attribute that you have. Delete it or do something like this:

[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]