ADO.NET Entity Framework: How to get auto cascading deletions?

Question

I am using the Ado.Net Entity Framework with ASP.NET MVC.

In my MSSQL 2008 Database I have for example the following simplified tables and relations:

 (Song) 1--* (Version) 1 -- 1 (VersionInfo)

Is it possible to automatically have the linked Versions and their VersionInfo's deleted when I delete a Song?

Currently I am using something like the following code, which is a lot of manual work, since some tables have up to 8 relations and those relations have subrelations too sometimes:

  db = new Database() //Entities
  Song song = db.Song.First();

  Song.Version.Load();

  foreach(Version version in Song.Version.ToList())
  {
       //Remove Song-Version Reference.
       song.Version.Remove(version);


       //Get The VersionInfo so we can delete it after we deleted the Version object.
       version.VersionInfoReference.Load();
       VersionInfo versionInfo = version.VersionInfo;

       //Now we can delete the Version Object.
       db.DeleteObject(version);
       //Now we can also delete the versionInfo, since the reference is gone.
       db.DeleteObject(versionInfo);

  }

  db.DeleteObject(song);

There must be an easier way to get cascading deletions. I already tried setting the relationship setting in MSSQL to Cascade when Deleting, but it didn't do a thing... Did I miss something there?

Anyway, how do other people solve this problem?

Answer 1

If you set it correctly in the database then it should definitely cascade the deletes. I think they improved this in the next version of the Entity Framework but I am not certain. I just remember seeing cascade somewhere. I'd recommend you have a look in the database again.

For instance, are there any other relations that also needs to be cascaded?

Answer 2

I tried it with a simpler database, 2 tables, and found out that cascade is only from the 1 side of a 1-many.

table A
id  - int
b_id - int 

table B
id - int

Relationship is set between A.b_id and B.id. Delete rule is cascade.

When I delete A, B is not deleted. When I delete B, A is deleted.

Problem is, I want to have B deleted when I delete A. I guess that is only possible manually.

Answer 3

You should not be doing this in the Entity Framework. All popular relational databases support ON CASCADE DELETE on foreign keys which is a lot more efficient as well. I suggest you just go with that.

It appears in your case you may have to cascade deletes in song to version and deletes in version to version info. Just load up the table designer in SQL Manager and you should see the relevant options under relationships.