Fix for "multiple cascade paths" error results in "foreign key constraint" error in SQLite unit tests

c# entity-framework-core sqlite xunit.net

Question

I have a database with two different paths to a certain entity, one path with DeleteBehavior.Cascade and the other with DeleteBehavior.Restrict to avoid a multiple cascade paths error on database creation. This is working as I expected when running locally using IIS Express and Visual Studio's SQL Server in memory, but throwing an error in unit tests testing the same functionality.

Example:

public class Account
{
    public string Id { get; set; }
}

Each account can have subscriptions:

public class Subscription
{
    public string Id { get; set; }

    // relationships

    public string AccountId { get; set; }

    public Account Account { get; set; }
}

Both accounts and subscriptions can have contacts (this might not make much sense but it's just an example, in reality my model is more complicated but it essentially boils down to this):

public class Contact
{
    public string Id { get; set; }

    // relationships

    public string AccountId { get; set; }

    public Account Account { get; set; }

    public string SubscriptionId { get; set; }

    public Subscription Subscription { get; set; }
}

I want any contacts to be deleted if either the account or the subscription is deleted. This is my model builder config for contacts:

public void Configure(EntityTypeBuilder<Contact> entity)
{
    entity.HasOne(e => e.Account)
        .WithMany()
        .HasForeignKey(e => e.AccountId)
        .OnDelete(DeleteBehavior.Restrict);

    entity.HasOne(e => e.Subscription)
        .WithMany()
        .HasForeignKey(e => e.SubscriptionId)
        .OnDelete(DeleteBehavior.Cascade);
}

config for subscriptions:

public void Configure(EntityTypeBuilder<Subscription> entity)
{
    entity.HasOne(e => e.Account)
        .WithMany()
        .HasForeignKey(e => e.AccountId)
        .OnDelete(DeleteBehavior.Cascade);
}

The thought being that when an account is deleted this will delete the subscriptions via the Cascade delete behavior, and this will delete the contacts, with the Restrict behavior between accounts and contacts solving the "multiple cascade paths" error.

This works when I'm running locally, I can delete an account and all subscriptions and contacts are deleted, no error. The issue is in the unit tests (using xUnit), which are using SQLite in memory. I want to test that deleting an account will delete all contacts:

[Fact]
public async Task DeleteAccount_ContactIsDeleted()
{
    using (var factory = new ContextFactory()) // same connection will be used within using block
    {
        using (var context = factory.CreateContext())
        {
            await context.SeedDatabaseOneContactAsync(); // inserts account, subscription and contact into database
        }

        using (var context = factory.CreateContext())
        {
            Assert.Single(context.Contacts);
            Assert.Single(context.Subscriptions);
            Assert.Single(context.Accounts);

            var account = await context.Accounts.SingleAsync();
            context.Remove(account);
            await context.SaveChangesAsync();

            Assert.Empty(context.Contacts);
            Assert.Empty(context.Accounts);
            Assert.Empty(context.Subscriptions);
        }
    }
}

However this throws the following error

Message: Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while updating the entries. See the inner exception for details. ---- Microsoft.Data.Sqlite.SqliteException : SQLite Error 19: 'FOREIGN KEY constraint failed'.

on the line

await context.SaveChangesAsync();

I'd like to know if this is an issue with SQLite, or if there's something I'm doing wrong with my model or config?

Note: I'm using Entity Framework Core 2.1.1, and these relationships are required (can't be nullable).

1
1
8/20/2018 1:11:00 PM

Popular Answer

Not sure if this is your problem but, I had the same error when the fluent API relationship config did not match the actual table structure.

In my case the fluent API setup a one to one relationship but the actual underlying table structure was a one to many relationship.

0
11/14/2018 7:41:09 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow