Cascade Delete with Entity Framework

Question

I have these two models:

public partial class Country
{
        public Country()
        {
            this.Dinners = new HashSet<Dinner>();
        }

        public int CountryID { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Dinner> Dinners { get; set; }
}

and

public partial class Dinner
{
        public Dinner()
        {
            this.TRsvps = new HashSet<TRsvp>();
        }

        public int DinnerID { get; set; }
        public System.DateTime EventDate { get; set; }
        public string Description { get; set; }
        public string HostedBy { get; set; }
        public Nullable<int> CountryID { get; set; }

        public virtual Country Country { get; set; }
}

I got a bit confused on how Entity Framework will act when a user tries to delete a parent entity (in our case it is the Country entity) that has child records (Dinners).

For example if I have the following code inside my mvc action method:-

public ActionResult DeleteConfirmed(int id)
{
    Country country = db.Countries.Find(id);
    db.Countries.Remove(country);
    db.SaveChanges();
    return RedirectToAction("Index");
}

And exception will be raised if I try to remove a country which has dinners, which sounds valid.

I tried modifying my code as follow, by including the Dinners when retrieving the Country object:

Country country = db.Countries.Include(a => a.Dinners).Single(a2 => a2.CountryId = id);
db.Countries.Remove(country);
db.SaveChanges();
return RedirectToAction("Index");

No exception will be raised, so I thought that EF would have deleted the child dinners, but what happens is that it updates the countryID FK inside the Dinners table to be null.... (Cascade Set to Null)

I tried looping over the Dinners collection as follows:

 public ActionResult DeleteConfirmed(int id)
 {
     Country country2 = db.Countries.Find(id) ;

     foreach(var d in country2.Dinners)
     {
         db.Dinners.Remove(d);
     }

     db.Countries.Remove(country2);
     db.SaveChanges();
     return RedirectToAction("Index");
}

but this raised the following error:

An exception of type 'System.InvalidOperationException' occurred in System.Core.dll but was not handled in user code

Additional information: Collection was modified; enumeration operation may not execute.

I realized that I should explicitly call the .Tolist() on the foreach to get the parent and all its children deleted as follows:

 foreach(var d in country2.Dinners.ToList())

Can anyone advice if I getting things wrong, or this is the only way to support cascade on delete using EF ?

Thanks

Have you enabled cascaded deletes on your model? – DavidG Oct 31 '14 at 14:33 — DavidG, Oct 31 '14 at 14:33

score 1 · Answer 1 · answered Oct 31 '14 at 14:35

1

If you want your deletes to cascade automatically, in your OnModelCreating method, you need to manually enable it:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Country>().WillCascadeOnDelete(true);
}

answered Oct 31 '14 at 14:35

DavidG

113,891
12
217
223

but this will affect all the models is this correct ? while in my case only admin users are able to perform such a deletion, and normal users will not be able to delete parent records unless they do not contain child records,, – John John Oct 31 '14 at 14:41
so i need to control this on action method basis ,, – John John Oct 31 '14 at 14:42
Yes, that's true. But you should have the correct security on the action method anyway. – DavidG Oct 31 '14 at 14:43
yes i have applied a CustomeAuthorizarion attribute so users with admin roles will only be able to call an action method that do a Cascade delete.. – John John Oct 31 '14 at 14:49
Correct me if I'm wrong, but I don't think you need to manually enable cascade on delete if you make the foreign key an `int` rather than an `int?` – Colin Oct 31 '14 at 14:59
@Colin Making it a nullable int will make the relationship optional though, not necessarily what is required. – DavidG Oct 31 '14 at 15:01
1

Yes, my point is that it is already nullable and should be changed to non-nullable to make it a required relationship - which is cascade on delete by default – Colin Oct 31 '14 at 15:01
aha ok you pointed out an important thing, but then the delete will have a Cascade delete when i use the .Include ? is this correct ? – John John Oct 31 '14 at 15:25

score 1 · Answer 2 · edited May 23 '17 at 10:30

1

You are describing the documented behaviour for cascade delete when the foreign key is nullable:

If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null.

If you want it to cascade delete then the relationship is required and the foreign key should not be nullable. Change public Nullable<int> CountryID { get; set; } to public int CountryID { get; set; }

Reference:

http://msdn.microsoft.com/en-us/data/jj591620.aspx#CascadeDelete

Additional info following your comment You don't have to .Include to get cascade delete to work on required relationships i.e. once you have made the foreign key non-nullable. I am sure of this because that is how my application works.

I think you are observing that Remove marks your entire object graph for removal - whether required or not - in the same way that Add marks the entire graph for insertion. NB - I am not 100% sure of this bit so you should test this before you rely on it.

Further reading here:

using Dbset.Add Versus using EntityState.Added

Why Does Entity Framework Reinsert Existing Objects into My Database?

What is the difference between IDbSet.Add and DbEntityEntry.State = EntityState.Added?

edited May 23 '17 at 10:30

Community

1
1

answered Oct 31 '14 at 14:55

Colin

22,328
17
103
197

but to Cascade delete then i have to use .Include() as follow :- "Country country = db.Countries.Include(a => a.Dinners).Single(a2 => a2.CountryId = id);" , is this correct ? – John John Oct 31 '14 at 15:26
i think if you pass the whole object to the POST delete action method including all the Navigation properties, then .Remove will do a cascade on delete,, but in my action method i do not pass the whole object with its navigation properties, i just pass the object ID and then i retrieve it from the db, so i think .Include is required in my case to force cascade on delete,, i need to test this ... – John John Oct 31 '14 at 16:50
now if i changed the FK on the dinner table to be int instead of int?, and i wrote the following db.Countries.Remove(country), i will get the following exception ;-Additional information: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must – John John Oct 31 '14 at 17:19

Cascade Delete with Entity Framework

2 Answers2