Entity Framework Core Code-First: Cascade delete on a many-to-many relationship

Viewed 11757

I'm working on an ASP.NET MVC 6 project with Entity-Framework Core (version "EntityFramework.Core": "7.0.0-rc1-final") backed by a SQL Server 2012 express DB.

I need to model a many-to-many relationship between a Person entity and an Address entity. As per this guide I modeled it with a PersonAddressjoin-table entity, because this way I can store some extra info.

My goal is to set-up my system this way:

  • If a Person instance is deleted, all the related PersonAddress instances must be deleted. All the Address instances they reference to must be deleted too, only if they are not related to other PersonAddress instances.
  • If a PersonAddress instance is deleted, the Address instance it relates to must be deleted only if it is not related to other PersonAddress instances. All Person instances must live.
  • If an Address instance is deleted, all the related PersonAddress instances must be deleted. All Person instances must live.

I think most of the work must be done in the many-to-many relationship between Person and Address, but I expect to write some logic too. I will leave this part out of this question. What I'm interested in is how to configure my many-to-many relationship.

Here is the current situation.

This is the Person entity. Please note that this entity has got one-to-many relationships with other secondary entities.

public class Person
{
    public int Id {get; set; } //PK
    public virtual ICollection<Telephone> Telephones { get; set; } //navigation property
    public virtual ICollection<PersonAddress> Addresses { get; set; } //navigation property for the many-to-many relationship
}

This is the Address entity.

public class Address
{
    public int Id { get; set; } //PK
    public int CityId { get; set; } //FK
    public City City { get; set; } //navigation property
    public virtual ICollection<PersonAddress> People { get; set; } //navigation property
}

This is the PersonAddress entity.

public class PersonAddress
{
    //PK: PersonId + AddressId
    public int PersonId { get; set; } //FK
    public Person Person {get; set; } //navigation property
    public int AddressId { get; set; } //FK
    public Address Address {get; set; } //navigation property
    //other info removed for simplicity
}

This is the DatabaseContext entity, where all the relationships are described.

public class DataBaseContext : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<Address> Addresses { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {            
        //All the telephones must be deleteded alongside a Person.
        //Deleting a telephone must not delete the person it refers to.
        builder.Entity<Person>()
            .HasMany(p => p.Telephones)
            .WithOne(p => p.Person);

        //I don't want to delete the City when I delete an Address
        builder.Entity<Address>()
            .HasOne(p => p.City)
            .WithMany(p => p.Addresses)
            .IsRequired().OnDelete(Microsoft.Data.Entity.Metadata.DeleteBehavior.Restrict);

        //PK for the join entity
        builder.Entity<PersonAddress>()
            .HasKey(x => new { x.AddressId, x.PersonId });

        builder.Entity<PersonAddress>()
            .HasOne(p => p.Person)
            .WithMany(p => p.Addresses)
            .IsRequired();

        builder.Entity<PersonAddress>()
            .HasOne(p => p.Address)
            .WithMany(p => p.People)
            .IsRequired();
    }
}

Both Telephone and City entities have been removed for the sake of simplicity.

This is the code for removing a Person.

Person person = await _context.People.SingleAsync(m => m.Id == id);
try
{
    _context.People.Remove(person);
    await _context.SaveChangesAsync();
}
catch (Exception ex)
{

}

As for my readings avoiding .Include() will let the DB take care of the eventual CASCADE deletes. I'm sorry but I don't remember the SO question where this concept was clarified.

If I run this code I can seed the DB using this workaround. When I want to test-deleting a Person entity with the above code, I get this exception:

The DELETE statement conflicted with the REFERENCE constraint "FK_PersonAddress_Person_PersonId". The conflict occurred in database "<dbName>", table "<dbo>.PersonAddress", column 'PersonId'.
The statement has been terminated.

I tested several relationship setups in the DatabaseContext.OnModelCreating method without any luck.

Finally, here's my question. How should I configure my many-to-many relationship in order to correctly delete a Person and its related entities from my application, according to the goal described before?

Thank you all.

1 Answers
Related