Removing an entire object graph, including relationships, using EF First code

Question

I have classes that are structured as follows:

public class Forecast { [Key] [ForeignKey("Stop")] public string Abbreviation { get; set; } public virtual Stop Stop { get; set; } public virtual List<Direction> Directions { get; set; } } public class Direction { public int DirectionId { get; set;} public string Abbreviation { get; set;} public virtual Forecast Forecast { get; set;} public virtual List<Transport> Transports { get; set;} } public class Transport { public int TransportId { get; set; } public int DirectionId { get; set;} public virtual Direction Direction { get; set;} } public partial class Stop { [Key] public string Abbreviation { get; set; } public virtual Forecast Forecast { get; set; } } 

I developed these classes and used EF Code First 4.1 to create the database. It seems that CF correctly creates all the primary and foreign key relationships between classes in the database (MSSQL).

My problem is when I want to delete the Forecast. I thought I could do something like the following:

  using (MyContext ctxt = new MyContext()) { // get a forecast, somehow, not really important // The one assumption is I'm absolutely sure it // Abbreviation key already exists in the database // and the list of Forecasts. Forecast f; ctxt.Forecasts.Remove(f); } 

This removes the top-level object from the database just fine. However, all its children — all directions and transports — remain in the database and become orphans (their key relationship column is null. I expect, but I DON’T know why they are not just deleted). I resorted to recursively calculating the object graph and calling Remove on each object from the corresponding DbSet to ctxt, but this seems to be ... the wrong way to do this.

What am I missing here?

Why can't I just say

ctxt.Forecasts.Remove (e);

and do with it?

Edit:

@ Ladislav gave me the correct answer - I need to add [Required] to the Abbreviation as directed.

However, I still have to actually load the child objects for this to work - something simple, like

Direction d = f. Directions [0];

will cause the deletion to actually delete the child objects. I am well aware that this is due to lazy loading. I thought that FK and ON CASCADE DELETE was that you would not have to actually load objects to delete them?

Again, I seem to be missing something simple.

Answer 1

@Eranga is right that this is done using the ON DELETE CASCADE parameter for the relationship in the database, but you use the first approach to the code, and EF creates the database for you, so the problem is that your model is incorrectly defined because EF didn't create a cascading rule for you.

Why? Because of this:

 public class Direction { public int DirectionId { get; set; } public string Abbreviation { get; set; } public virtual Forecast Forecast { get; set; } public virtual List<Transport> Transports { get; set; } } 

Abbreviation is a FK property and it is NULL! So EF looks at your model and sees that you have defined a Direction object that can have an Abbreviation value of null, and because of this it can exist as an orphan. Change it to:

 public class Direction { public int DirectionId { get; set; } [Required] public string Abbreviation { get; set; } public virtual Forecast Forecast { get; set; } public virtual List<Transport> Transports { get; set; } } 

and removing Forecast will remove all associated Direction and Transport instances. Stop is a different story because it is the parent Forecast object, so it will never be deleted using Forecast .

Edit:

Another point - you do not want to add ON DELETE CASCADE to your relationship manually, because EFs must know about allowed cascading deletes. EF uses this information if you have related objects associated.

If you put the rule manually in the database, you should use free mapping and tell EF about this rule. When you forcefully delete a cascade in a fluent api, you do not need to manually make it in the database - it will be created automatically during the rest of the database.

Answer 2

You can easily achieve this by setting ON DELETE CASCADE when creating foreign keys in the database.