Incomplete EF code-first cascade delete on many to many relationship

.net c# ef-code-first entity-framework entity-framework-6

Question

I have a PhoneNumber entity which I'd like to reference across multiple entities. For example, a Contact entity that has many PhoneNumbers, and a Business entity with one PhoneNumber.

public class PhoneNumber
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Number { get; set; }
}

public class Contact
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public virtual ICollection<PhoneNumber> PhoneNumbers { get; set; }
}

public class Business
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [ForeignKey("PhoneNumber")]
    public int PhoneNumberId { get; set; }
    public virtual PhoneNumber PhoneNumber { get; set; }        
}

I've setup Contact and Business so that they have one way navigation properties. Also, the phone numbers are optional. I've also setup the many relationship for Contact to prevent EF from adding a Contact_Id column when I add a migration. The mapping is as follows (note WithMany() is used since PhoneNumber doesn't have a nav property back to Contact):

modelBuilder.Entity<Contact>().HasMany(r => r.PhoneNumbers).WithMany()
            .Map(x => x.MapLeftKey("ContactId").MapRightKey("PhoneId"));

When I add a Contact with multiple phone numbers it gets added fine. There are records for the Contact, the PhoneNumbers table, and the ContactPhoneNumbers link table.

However, the issue I'm struggling with is when I delete a contact. EF correctly deletes the entry in the ContactPhoneNumbers link table, and the Contact entry, but it doesn't delete the entries from the PhoneNumbers table. I've seen examples of mapping with modelBuilder where WillCascadeOnDelete(true) is used, but that option isn't available when using WithMany().

What do I need to do to get that type of cascade delete working correctly? Is it possible with this setup? Or will I need to have a separate PhoneNumbers table for each entity (Contact and Business) to setup a relationship where the respective PhoneNumber table uses a FK (eg., Contact_Id)?

I'm fairly new to EF so any suggestions are welcome. I might be going about this entirely wrong.

EDIT: here's the related migration code...

CreateTable(
    "dbo.PhoneNumbers",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Number = c.String()
        })
    .PrimaryKey(t => t.Id);

CreateTable(
    "dbo.Contacts",
    c => new
        {
            Id = c.Int(nullable: false, identity: true)
        })
    .PrimaryKey(t => t.Id);

CreateTable(
    "dbo.ContactPhoneNumbers",
    c => new
        {
            ContactId = c.Int(nullable: false),
            PhoneId = c.Int(nullable: false),
        })
    .PrimaryKey(t => new { t.ContactId, t.PhoneId })
    .ForeignKey("dbo.Contacts", t => t.ContactId, cascadeDelete: true)
    .ForeignKey("dbo.PhoneNumbers", t => t.PhoneId, cascadeDelete: true)
    .Index(t => t.ContactId)
    .Index(t => t.PhoneId);

CreateTable(
    "dbo.Business",
    c => new
        {
            Id = c.Int(nullable: false),
            PhoneNumberId = c.Int(nullable: false)
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.PhoneNumbers", t => t.PhoneNumberId, cascadeDelete: true)
    .Index(t => t.Id)
    .Index(t => t.PhoneNumberId);
1
1
5/1/2014 1:49:26 AM

Accepted Answer

In order for the cascading delete to work records that are going to be cascaded must have a foreign key back to the record being deleted. So in your example, You delete a Contact record. Because their is a foreign key from ContactPhoneNumber to Contact, the cascade works. Since there is no foreign key from PhoneNumber to ContactPhoneNumber, (the foreign key goes the other way) the cascade does not continue.

This is because you defined the relationship as a many to many. If you think about trying to perform a cascading delete on your model as you would like, If a ContactPhoneNumber is deleted and then its associated PhoneNumbers are deleted, there could now be other ContactPhoneNumbers that that don't have a valid PhoneNumber (Because there can be many ContactPhoneNumbers to one PhoneNumber). Now these would need to be deleted and this process would continue. Databases don't like this cyclical cascading.

It is not entirely clear to me why you need the many to many relationship, If you truly need it you will not be able to perform a cascade on delete. If you can make your relationship:

1 Contact - * ContactPhoneNumber 1- * PhoneNumber, then you could configure the cascade to work like you want it too.

2
5/1/2014 1:39:16 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