Ef code first complicates the cascade deletion script

Question

I have this scenario: I have these classes:

public class A { public int Id {get;set;} public virtual ICollection<B> bCollection {get; set; } } public class B { public int Id {get;set;} } public class C1 : BaseClass1 { public int Id{get;set;} public virtual BB{get;set;} } public class C2 : BaseClass2 { public int Id {get;set;} public virtual BB {get;set;} } ... public class C100 : BaseClass100 { public int Id {get;set;} public virtual BB {get;set;} } 

class A has a set of classes B, while class Ci has one class B and different base classes . when there is only B in the collection of class A that Ci does not reference it, I can delete class A and also delete all collections of B (cascading deletion). But , when there is B in the collection of class A that the Ci classes have a reference to it, I cannot delete an instance of class A ...

Expected Behavior:

Class A will be deleted, and the entire collection B that class A has, and if Ci refers to some of B in the collection, it will be null at the end of the deletion. (Ci classes will not be deleted!), also I do not want to iterate over my entire Ci class to see if there is a link to assembly B that needs to be removed.

I do not need this code:

 MyDbContext db=new MyDbContext(); Hash<int> bCollectionToDelete=GetBCollectionToDeleteHashSet(); var C1_db_collection= db.C1Collection.ToList(); foreach(var c1Item in C1Collection) { if(bCollectionToDelete.Contains(c1Item.refrenceIdToB) { c1Item.refrenceIdToB=null; } } var C2_db_collection= db.C2Collection.ToList(); foreach(var c2Item in C1Collection) { if(bCollectionToDelete.Contains(c2Item.refrenceIdToB) { c2Item.refrenceIdToB=null; } } ... var C100_db_collection= db.C100Collection.ToList(); foreach(var c100Item in C100Collection) { if(bCollectionToDelete.Contains(c100Item.refrenceIdToB) { c100Item.refrenceIdToB=null; } } 

Does anyone know how to achieve it?

Answer 1

This may be the case when you write a stored procedure to process your cascading delete logic, and the EF code just makes one call to the “DeleteMyObject” sp, and then your ef code will need to update its context so that it reloads immediately after.

You will pay a small performance penalty to update your context, but if I had to guess by moving the logic to sp, and the performance gain between interconnected deletes there would more than compensate for the small performance penalty when used for updating.

Not sure if SP is an option for you, but they can be used when you need it.

EDIT:

A very simplified example of a stored procedure that takes the primary key of the "MainTable" table and then deletes any related records, if any, from the 3 "child tables". Actual logic may be a little more complicated:

 CREATE PROCEDURE DeleteMyObject @Id INT as BEGIN TRAN delete from ChildTable1 WHERE ParentId=@Id delete from ChildTable2 WHERE ParentId=@Id delete from ChildTable3 WHERE ParentId=@Id delete from MainTable WHERE ID=@Id COMMIT TRAN 

An example of an SP call that does not map to your EF model:

SqlParameter param1 = new SqlParameter ("@Id", 12345); context.Database.ExecuteSqlCommand ("DeleteMyObject @Id", param1);

Further reading: http://msdn.microsoft.com/en-us/data/gg699321.aspx

Answer 2

Instead of searching in all classes of C, you can let B delete it yourself

using interface

 public interface IRemoveB { void RemoveB(); } public class C1 : BaseClass1, IDeleteB { public int Id { get; set; } public virtual BB { get; set; } #region IDeleteB Member public void RemoveB() { this.B = null; } #endregion } public class B { public int Id { get; set; } public ICollection<IDeleteB> list{ get; set; } } public class A { public int Id { get; set; } public virtual ICollection<B> bCollection { get; set; } public void prepareForDelete() { foreach (var item in bCollection) { foreach (var deref in item.list) { deref.RemoveB(); } } } } 

with the help of a delegate

 public class A { public int Id { get; set; } public virtual ICollection<B> bCollection { get; set; } public void prepareForDelete() { foreach (var item in bCollection) { item.RemoveMe(); } } } public class B { public int Id { get; set; } public event EventHandler Remove; public void RemoveMe() { EventHandler removeHandler = Remove; if (removeHandler != null) { removeHandler(this, EventArgs.Empty); } } } public class C2 : BaseClass2 { public int Id { get; set; } private B internB; // maybe you need an otherform to set B because of your virtual public BB { get { return internB; } set { if (internB != value) if (internB != null) internB.Remove -= this.RemoveB; else if(value != null) value.Remove += this.RemoveB; internB = value; } } public void RemoveB(object sender, EventArgs args) { internB.Remove -= this.RemoveB; B = null; } } 

Edit:

to answer your question

You mean thet every ci inherit from the same interface?
Yes exactly. Interface Details

and if yes ICollection<IDeleteB> is stored in the database?
No, it exists only in your program. Think of it as a temporary 1: N reviewer

and in the delegate example how it is store in the database?
No, basically, it looks like a collection

and this only works if B in A == B in C , which means the same instance of class B

Answer 3

you can set the delete cascade to true for each Ci class in the OnModelCreating method by adding the following

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { // for each class Ci write the following modelBuilder.Entity<Ci>() .HasRequired(t=>TB) .WithMany() .WillCascadeOnDelete(true); } 

this removal will remove Ci for you if exists with class B hope this helps you