I have a peculiar problem with EFCores SQLite framework, which works fine for PostgreSQL. So there is this entity:

[PrimaryKey(nameof(InterviewProcessId), nameof(Position))]
public record StageEntity
{
    public InterviewProcessEntity InterviewProcess { get; set; } = null!;
    public Guid InterviewProcessId { get; set; }
    public int Position { get; set; }
}

Which is referenced like this:

[PrimaryKey(nameof(StageInterviewProcessId), nameof(StagePosition), nameof(TalentUserId))]
public record TalentStageEntity
{
    public StageEntity Stage { get; set; } = null!;
    public Guid StageInterviewProcessId { get; set; }
    public int StagePosition { get; set; }
    public Guid TalentUserId { get; set; }
}

// model builder
modelBuilder.Entity<TalentStageEntity>(entity =>
{
    entity.HasKey(e => new { e.StageInterviewProcessId, e.StagePosition, e.TalentUserId });
    entity.HasOne(e => e.Stage)
        .WithMany(e => e.TalentStages)
        .HasForeignKey(e =>  new { e.StageInterviewProcessId, e.StagePosition });
});

Npqsql will create a correct database schema from that. SQLite however, pulls an ID for StageEntity out of thin air. Here is a comparison (marked yellow):

SQLite then proceeds to throw this error:

SQLite Error 1: ‘foreign key mismatch – “ef_temp_TalentStages” referencing “Stages”‘.

I am not really sure what to do differently. I’ve tried making the relatioship bidirectional by adding IList<TalentStageEntity> TalentStages and this to the model builder:

modelBuilder.Entity<StageEntity>(entity =>
{
    entity.HasKey(e => new { e.InterviewProcessId, e.Position });
    entity.HasOne(e => e.InterviewProcess)
        .WithMany(e => e.Stages)
        .HasForeignKey(e =>  e.InterviewProcessId);
});

I tried defining everything with attributes and everything with model builder and everything with both. Now I’m out of ideas.

Does anyone know what is wrong?