CODEWITHSUNDEEP
c#entity-framework-6
Dumbledore
Dumbledore

Reputation: 460

Entity Framework: Cascade delete works other way round than expected

I have 3 entities: Address, Student and Faculty. Here is the relationship diagram:

enter image description here

Entity Classes:

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }
}

public class Faculty
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public int Id { get; set; }
    public string Street { get; set; }
    public Student Student { get; set; }
    public Faculty Faculty { get; set; }
}

In my Context file, I'm defining defining the relationships and setting the cascade delete to true:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

    modelBuilder.Entity<Student>()
        .HasRequired(s => s.Address)
        .WithOptional(a => a.Student)
        .WillCascadeOnDelete(true);

    modelBuilder.Entity<Faculty>()
        .HasRequired(s => s.Address)
        .WithOptional(a => a.Faculty)
        .WillCascadeOnDelete(true);
}

My use case is that when I delete a Student or a Faculty object, I want the associated Address to be deleted as well. However, it works other way round.

Any thoughts/suggestions on this?

Upvotes: 4

Views: 222

Answers (4)

David T. Macknet
David T. Macknet

Reputation: 3162

At what point, logically, would you want to have an address without a person associated? You wouldn't. Do you want to allow multiple addresses for a given person? If not, then slap all of these fields into the same table and be done - that's essentially what you're saying: that you have to have both a person and an address, so why separate them if you're not going to a) allow multiple, or b) allow zero? Putting them into the same table, in this scenario, is 3rd Normal Form, and is adequate to most uses.

Upvotes: 2

Jonathan Allen
Jonathan Allen

Reputation: 70277

The correct answer is to move all of the Address columns into the Student/Faculty table.

Then to make EF happy with your object model, you use the ComplexType attribute on the Address class.

https://msdn.microsoft.com/en-us/data/jj591583.aspx?f=255&MSPPError=-2147217396

Part of the reason I say this is the "correct answer" is that you haven't addressed the problem of two students sharing the same address record. Nothing in your object model prevents it from happening, and if it does then what happens when you try to delete one student?

Upvotes: 1

Jonathan Allen
Jonathan Allen

Reputation: 70277

If you don't want to change your table schema, you have two options:

  1. Only perform deletes via a stored procedure. Since this is storage logic, not business logic, it makes sense to encapsulate the deletion rules in a procedure.

  2. Use an ON DELETE trigger. I personally don't like using triggers this way, but it is a possibility and would allow you to continue using EF unchanged.

Upvotes: 0

T Gray
T Gray

Reputation: 712

The real issue is that you have not modeled what you are looking for correctly. There is no semantic difference between your student and faculty tables. Therefore, you are relationally incorrect in assuming that a migratory foreign key will suffice. In other words, the master in your master detail tables is address. Your model shows that there is a one to many relationship between address and faculty as well as one between address and student. Therefore, the ONLY way a cascade delete will work is by first deleting the address. In fact, I would probably add the type as a sub-table so that a person can have multiple roles with multiple addresses.

I would model it differently. I would say PERSON(ID, NAME, TYPE) => 0,1 ADDRESS(ID, STREET). In which case, if you delete a person (which can be either faculty or student), the corresponding address(es) will also be deleted.

This ably demonstrates the disconnect between OO and Relational. Taking a purely OO approach to data storage can get a bit strange.

Upvotes: 2

Related Questions