Entity Framework Core on SQL Server: Failing to delete an optionally self-referenced record

c# entity-framework-core foreign-keys sql-server

Question

Given a database, tracking persons and their optional spouse as self referencing foreign key:

public class Person
{
    public int Id { get; set; }

    public string Name { get; set; }

    public int? SpouseId { get; set; }
}

Entity Framwork Core DbContext looks like this, notice the DeleteBehavior.SetNull:

public class PersonsContext : DbContext
{
    public PersonsContext(DbContextOptions<PersonsContext> options) : base(options) {}

    public DbSet<Person> Persons { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder
            .Entity<Person>()
            .HasOne(typeof(Person))
            .WithOne()
            .HasForeignKey(typeof(Person), nameof(Person.SpouseId))
            .IsRequired(false)
            .OnDelete(DeleteBehavior.SetNull);
    }
}

This does not even generate a model. The error says:

Introducing FOREIGN KEY constraint 'FK_Persons_Persons_SpouseId' on table 'Persons' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.


Okay, second attempt. We're taking care of breaking the reference on our own. The FK will be modelled with DeleteBehavior.Restrict:

public class PersonsContext : DbContext
{
    public PersonsContext(DbContextOptions<PersonsContext> options) : base(options) {}

    public DbSet<Person> Persons { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder
            .Entity<Person>()
            .HasOne(typeof(Person))
            .WithOne()
            .HasForeignKey(typeof(Person), nameof(Person.SpouseId))
            .IsRequired(false)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

A simple test tries to null out the reference on both sides, and then deletes a married person, leaving the other person without a SpouseId:

[Fact]
public void Manually_Remove_Reference()
{
    using (var personsContext = new PersonsContext(DbContextOptions))
    {
        var him = new Person {Id = 1, Name = "Him", SpouseId = 2};
        var her = new Person {Id = 2, Name = "Her", SpouseId = 1};

        personsContext.Persons.Add(him);
        personsContext.Persons.Add(her);
        personsContext.SaveChanges();
    }

    using (var personsContext = new PersonsContext(DbContextOptions))
    {
        var him = personsContext.Persons.Find(1);
        var her = personsContext.Persons.Find(2);
        him.SpouseId = null;
        her.SpouseId = null;
        personsContext.Persons.Remove(him);
        personsContext.SaveChanges();
    }

    using (var personsContext = new PersonsContext(DbContextOptions))
    {
        Assert.Null(personsContext.Find<Person>(1));
    }
}

Resulting in:

System.InvalidOperationException : Unable to save changes because a circular dependency was detected in the data to be saved: 'ForeignKey: Person {'SpouseId'} -> Person {'Id'} Unique, ForeignKey: Person {'SpouseId'} -> Person {'Id'} Unique'.

Stack Trace:

   at Microsoft.EntityFrameworkCore.Internal.Multigraph`2.BatchingTopologicalSort(Func`2 formatCycle)
   at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.TopologicalSort(IEnumerable`1 commands)
   at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.<BatchCommands>d__8.MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at Persistence.Tests.UnitTest1.Manually_Remove_Reference() in C:\Users\MarcWittke\source\scratchpad\Persistence\Persistence.Tests\UnitTest1.cs:line 58

Adding more SaveChanges() after nulling the references doesn't make a difference. WHat indeed works, is: breaking the reference in a separate DbContext instance, saving it, opening a new one and deleting the record. But this won't be atomic any more.

Full code: https://github.com/marcwittke/DeletingOptionallySelfReferencedRecord

1
1
5/9/2018 3:54:12 PM

Accepted Answer

Nevermind, the issue was my test setup, inserting the two persons. (note to myself: check line numbers in stack traces)

this code works as expected:

[Fact]
public void Manually_Remove_Reference()
{
    using (var personsContext = new PersonsContext(DbContextOptions))
    {
        var him = new Person { Name = "Him"};
        var her = new Person { Name = "Her"};

        personsContext.Persons.Add(him);
        personsContext.Persons.Add(her);
        personsContext.SaveChanges();

        // this must occur after inserting the two persons!!
        him.SpouseId = her.Id;
        her.SpouseId = him.Id;
        personsContext.SaveChanges();
    }

    using (var personsContext = new PersonsContext(DbContextOptions))
    {
        var her = personsContext.Persons.Find(2);
        her.SpouseId = null;

        var him = personsContext.Persons.Find(1);
        personsContext.Persons.Remove(him);
        personsContext.SaveChanges();
    }

    using (var personsContext = new PersonsContext(DbContextOptions))
    {
        Assert.Null(personsContext.Find<Person>(1));
    }
}
1
5/9/2018 5:03:07 PM

Popular Answer

Only one EnityState can be assigned to a tracked entity at any given time. So when you are setting him.SpouseId == null that entity has a state of EntityState.Modified, but as soon as you call personsContext.Remove(him) the state is now EntityState.Deleted. EF won't track order of state changes in this manner, only the current state of the entity.

To fix your issue you must call .SaveChanges() as soon as you modify the FK fields to null, then remove the entities and save the changes again.

using (var personsContext = new PersonsContext(DbContextOptions))
{
    var him = personsContext.Persons.Find(1);
    var her = personsContext.Persons.Find(2);
    him.SpouseId = null;
    her.SpouseId = null;

    personsContext.SaveChanges(); // Add this line

    personsContext.Persons.Remove(him);
    personsContext.SaveChanges();
}


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