Mirosoft.Data.Sqlite 7.0, SQLite Error 19: 'FOREIGN KEY constraint failed'

[Eruka]

Hi, I have a many-to-many relation between entities and it worked well in EF Core 6. After update to EF Core 7 main app also works as expected but the integration tests which use inmemory Sqlite fail with exception SQLite Error 19: 'FOREIGN KEY constraint failed'. Also in logs I can see next exception:

System.InvalidOperationException : The relationship from 'CompanyPlanSubscription.Company' to 'Company.CompanyPlanSubscriptions' with foreign key properties {'CompanyId' : int} cannot target the primary key {'Id' : Guid} because it is not compatible. Configure a principal key or a set of foreign key properties with compatible types for this relationship.

Relationships are defined in the configuration for company (EntityTypeBuilder<Company> builder):

  builder.HasAlternateKey(e => e.CompanyId);

  builder.HasMany(comp => comp.CompanyPlans)
                .WithMany(plan => plan.Companies)
                .UsingEntity<CompanyPlanSubscription>(
                    x => x.HasOne(sub => sub.CompanyPlan)
                            .WithMany(plan => plan.CompanyPlanSubscriptions)
                            .HasForeignKey(sub => sub.CompanyPlanId)
                            .OnDelete(DeleteBehavior.NoAction),
                    x => x.HasOne(sub => sub.Company)
                            .WithMany(comp => comp.CompanyPlanSubscriptions)
                            .HasForeignKey(sub => sub.CompanyId)
                            .HasPrincipalKey(comp => comp.CompanyId)
                            .OnDelete(DeleteBehavior.NoAction)
                    x => x.HasKey(t => new { t.CompanyId, t.CompanyPlanId });

public class Company
    {
        [Key]
        public Guid Id { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int CompanyId { get; set; }
        // .....
        public IList<CompanyPlanSubscription> CompanyPlanSubscriptions { get; set; } = new List<CompanyPlanSubscription>();
        public IList<CompanyPlan> CompanyPlans { get; set; } = new List<CompanyPlan>();
}

public class CompanyPlan
    {
        [Key]
        public CompanyPlanId Id { get; set; }        
        public IList<CompanyPlanSubscription> CompanyPlanSubscriptions { get; set; } = new List<CompanyPlanSubscription>();
        public IList<Company> Companies { get; set; } = new List<Company>();
    }

 public class CompanyPlanSubscription
    {
        public int CompanyId { get; set; }
        public CompanyPlanId CompanyPlanId { get; set; }
        public Company Company { get; set; }
        public CompanyPlan CompanyPlan { get; set; }
    }

Company creation in a tests class looks like:

var customer = new Company()
                {
                    CompanyPlanSubscriptions = new List<CompanyPlanSubscription>()
                    {
                        new CompanyPlanSubscription()
                        {
                            CompanyPlanId = CompanyPlanId.Customer,
                        },
                        new CompanyPlanSubscription()
                        {
                            CompanyPlanId = CompanyPlanId.Supplier,
                        },
                    },
                };
context.Add(customer);
context.SaveChanges();

Version information

Microsoft.Data.Sqlite version: 7.0.2 Target framework: .NET 7.0 Operating system: Windows 10

<PackageReference Include="Microsoft.Data.Sqlite" Version="7.0.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="7.0.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.2" />

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[Eruka]

SQLite.UpgradeToV7.zip

@ajcvickers Here some example which works on ef core 6 and doesn't work after upgrading to v7

The code works if I use primary key to define many to many relationship, but it fails if I want to use alternate key. HasPrincipal() doesn't work in v7 but works in v6

Thank you in advance

[ajcvickers]

@bricelam Looks like CreateFunction may be broken here.

[ajcvickers]

On further investigation, this seems to be related to the update pipeline changes in EF7. The code is dynamically creating a trigger. However, marking all tables with HasTrigger doesn't appear to change the code that the update pipeline generates.

/cc @roji

[ajcvickers]

Notes for triage: verified that reverting to the old SQL generator fixes this. This means #29916 should cover this case. See also https://github.com/dotnet/EntityFramework.Docs/issues/4241

@Eruka As a workaround, do something like this:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.ReplaceService<IUpdateSqlGenerator, SqliteLegacyUpdateSqlGenerator>();