[SQLite] AUTOINCREMENT not emitted for primary key with value conversion

[thomaslevesque]

I have the following model and DbContext:

public record struct ProductId(int Value);

public class Product
{
    public ProductId Id { get; set; }
    public required string Name { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public required string Name { get; set; }
}

public class CatalogDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data source=:memory:");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().Property(p => p.Id)
            .HasConversion<ProductIdConverter>()
            .ValueGeneratedOnAdd()
            .HasAnnotation("Sqlite:Autoincrement", true);
    }

    private class ProductIdConverter : ValueConverter<ProductId, int>
    {
        public ProductIdConverter()
            : base(id => id.Value, value => new ProductId(value))
        {
        }
    }
}

dotnet ef dbcontext script produces the following output:

CREATE TABLE "Categories" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Categories" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL
);


CREATE TABLE "Products" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Products" PRIMARY KEY,
    "Name" TEXT NOT NULL
);

AUTOINCREMENT is correctly emitted for Category.Id, but not for Product.Id, even though I explicitly added the Sqlite:Autoincrement annotation. The only real difference between the two is that Product.Id has a converter (and some explicit configuration that was not necessary for Category.Id).

I suspect it might be related to the !HasConverter(property) condition here, but since I explicitly add the annotation, it shouldn't matter...

Include provider and version information

EF Core version: 7.0.0
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: 7.0
Operating system: Windows 11
IDE: VS Code

[ajcvickers]

@bricelam I'm able to reproduce this, but the interesting thing is that the database is still generating values for the Id column--see my code below.

#nullable enable

public record struct ProductId(int Value);

public class Product
{
    public ProductId Id { get; set; }
    public required string Name { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public required string Name { get; set; }
}

public class CatalogDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .LogTo(Console.WriteLine, LogLevel.Information)
            .UseSqlite("Data source=db.db");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().Property(p => p.Id)
            .HasConversion<ProductIdConverter>()
            .ValueGeneratedOnAdd()
            .HasAnnotation("Sqlite:Autoincrement", true);
    }

    private class ProductIdConverter : ValueConverter<ProductId, int>
    {
        public ProductIdConverter()
            : base(id => id.Value, value => new ProductId(value))
        {
        }
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new CatalogDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.AddRange(new Product { Name = "p1" }, new Product { Name = "p2" }, new Category { Name = "c1" }, new Category { Name = "c2" });
            context.SaveChanges();
        }

        using (var context = new CatalogDbContext())
        {
            foreach (var product in context.Products)
            {
                Console.WriteLine(product.Id);
            }
            
            foreach (var category in context.Categories)
            {
                Console.WriteLine(category.Id);
            }
        }
    }
}

[thomaslevesque]

the interesting thing is that the database is still generating values for the Id column

It's because of this. Basically, for SQLite, INTEGER PRIMARY KEY means the same thing as ROWID, so it always generates a value. AUTOINCREMENT just changes how it generates the values: it ensures old ids are never reused.

[bricelam]

Related to #10228 (probably the ideal way to fix this)

[bricelam]

Note, this also repros on EF Core 6.0