EF Core Code First模式下如何配置SQLite自增整型主键以避免非空约束失败错误

Ah, I’ve dealt with this exact SQLite/EF Code First quirk before! The root issue is that SQLite requires auto-increment columns to be defined as INTEGER PRIMARY KEY (not just INT PRIMARY KEY), and EF’s default DatabaseGeneratedOption.Identity setup doesn’t always generate this exact schema for SQLite. Here’s how to fix it using Code First configurations:

1. Use Fluent API to Enforce SQLite’s Column Type #

Attributes alone might not cut it for SQLite’s strict requirements. Override the OnModelCreating method in your DbContext to explicitly set the Id column’s type to INTEGER and confirm it’s set to auto-generate on add:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<YourEntityName>()
        .Property(e => e.Id)
        .HasColumnType("INTEGER") // This is the critical line for SQLite
        .IsRequired()
        .ValueGeneratedOnAdd(); // Matches DatabaseGeneratedOption.Identity
}

If you prefer attribute-based configuration, combine your existing attributes with a Column attribute specifying the type:

public class YourEntityName
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column(TypeName = "INTEGER")]
    public int Id { get; set; }

    // Your other entity properties here...
}

2. Generate and Apply a Migration #

Since you can’t modify the database directly, use EF Migrations to update the schema properly. Run these commands in the Package Manager Console (or .NET CLI):

Package Manager Console: #

Add-Migration FixSQLiteAutoIncrementColumn
Update-Database

.NET CLI: #

dotnet ef migrations add FixSQLiteAutoIncrementColumn
dotnet ef database update

This will adjust the database schema to use INTEGER PRIMARY KEY for your Id column, which tells SQLite to automatically generate incrementing values when you insert new records.

3. Verify the Schema (Optional) #

To confirm it worked, you can use a tool like DB Browser for SQLite to check the table’s schema. You should see the Id column defined as:

Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

Note: SQLite’s INTEGER PRIMARY KEY implicitly auto-increments, so the AUTOINCREMENT keyword is optional but guarantees values are never reused (even if rows are deleted).

Why This Solves the Non-Null Error #

SQLite only auto-generates values for columns that are aliased to its internal ROWID—which only happens when the column is defined as INTEGER PRIMARY KEY. If EF created the column as INT instead, SQLite won’t auto-populate it, leading to the non-null constraint failure when you don’t provide an Id value. By forcing the column type to INTEGER in your Code First config, you ensure EF generates the correct schema that SQLite expects.

内容的提问来源于stack exchange，提问作者San