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

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);

I don't see anything about the ContactPhoneNumbers table. From your code, it appears that Contact has a direct link to the Phone numbers table. Can you post the mappings for ContactPhone number? — mmilleruva, Apr 30 '14 at 21:46
@mmilleruva it's the link table created by EF migrations as a result of the `modelBuilder` mapping in my post. I don't actually create it directly myself. — Ahmad Mageed, Apr 30 '14 at 21:50

score 2 · Accepted Answer · answered May 01 '14 at 13:39

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.

Thanks, that makes sense and is how I would expect a many:many to work. I suppose I saw the schema setup working out and thought it was right when I used `WithMany()` (i.e., no nav property on PhoneNumbers and being able to use it from `Business` and `Contact`). That said, you're right, it is currently many:many but my intent was for it to be 1:many. I'll have to change it and probably use a separate table where PhoneNumber is 1:1 for other entities (e.g., `Business`). — Ahmad Mageed, May 01 '14 at 15:57

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

1 Answers1

Linked