EntityFramework CodeFirst: CASCADE DELETE for the same table many-to-many relationship

Question

I have a problem deleting a record with EntityFramework and many-to-many relationships for the same object. Consider this simple example:

Entity:

public class UserEntity { // ... public virtual Collection<UserEntity> Friends { get; set; } } 

Fluent API Configuration:

 modelBuilder.Entity<UserEntity>() .HasMany(u => u.Friends) .WithMany() .Map(m => { m.MapLeftKey("UserId"); m.MapRightKey("FriendId"); m.ToTable("FriendshipRelation"); }); 

• Is it right that it is not possible to define Cascade Delete in the Fluent API?

• What is the best way to remove UserEntity like Foo ?

  Now he is looking for me, I need a Clear collection of Foo Friends , then I need to load all the other UserEntities that contain Foo in Friends , and then remove Foo from each list before I remove Foo from Users . But that sounds too complicated.

• Is it possible to directly access a relational table so that I can delete records like this

   // Dummy code var query = dbCtx.Set("FriendshipRelation").Where(x => x.UserId == Foo.Id || x.FriendId == Foo.Id); dbCtx.Set("FriendshipRelation").RemoveRange(query); 

Thanks!

Update01:

4. My best solution for this know-how problem is to simply run the raw sql command before calling SaveChanges :

    dbCtx.Database.ExecuteSqlCommand( "delete from dbo.FriendshipRelation where UserId = @id or FriendId = @id", new SqlParameter("id", Foo.Id)); 

   But the disadvantage of this is that if SaveChanges for some reason fails, FriendshipRelation already deleted and cannot be dropped. Or am I wrong?

Answer 1

Problem 1

The answer is pretty simple:

Entity Framework cannot determine cascading deletion when it does not know which properties belong to the relation.

In addition, in many ways, there is a third table that is responsible for managing relationships. This table must have at least 2 FK. You must configure cascading deletion for each FK, not for the entire table.

The solution creates a FriendshipRelation object. Like this:

 public class UserFriendship { public int UserEntityId { get; set; } // the "maker" of the friendship public int FriendEntityId { get; set; }´ // the "target" of the friendship public UserEntity User { get; set; } // the "maker" of the friendship public UserEntity Friend { get; set; } // the "target" of the friendship } 

Now you need to change UserEntity . Instead of a UserEntity set UserEntity it has a UserFriendship set. Like this:

 public class UserEntity { ... public virtual ICollection<UserFriendship> Friends { get; set; } } 

Let's look at the display:

 modelBuilder.Entity<UserFriendship>() .HasKey(i => new { i.UserEntityId, i.FriendEntityId }); modelBuilder.Entity<UserFriendship>() .HasRequired(i => i.User) .WithMany(i => i.Friends) .HasForeignKey(i => i.UserEntityId) .WillCascadeOnDelete(true); //the one modelBuilder.Entity<UserFriendship>() .HasRequired(i => i.Friend) .WithMany() .HasForeignKey(i => i.FriendEntityId) .WillCascadeOnDelete(true); //the one 

Generated Migration:

 CreateTable( "dbo.UserFriendships", c => new { UserEntityId = c.Int(nullable: false), FriendEntityId = c.Int(nullable: false), }) .PrimaryKey(t => new { t.UserEntityId, t.FriendEntityId }) .ForeignKey("dbo.UserEntities", t => t.FriendEntityId, true) .ForeignKey("dbo.UserEntities", t => t.UserEntityId, true) .Index(t => t.UserEntityId) .Index(t => t.FriendEntityId); 

To get all user friends:

 var someUser = ctx.UserEntity .Include(i => i.Friends.Select(x=> x.Friend)) .SingleOrDefault(i => i.UserEntityId == 1); 

It all works great. However, there is a problem in this mapping (which is also happening in your current mapping). Suppose "I" is a UserEntity :

• I made a friend request to John - John accepted
• I asked a friend to turn to Ann Ann.
• Richard turned to me with a friend - I accepted

When I get the Friends property, it returns “John,” “Anne,” but not “Richard.” What for? because Richard is the "creator" of the relationship, not me. The Friends property is tied to only one side of the relationship.

Ok How can i solve this? Easy! Change the UserEntity class:

 public class UserEntity { //... //friend request that I made public virtual ICollection<UserFriendship> FriendRequestsMade { get; set; } //friend request that I accepted public virtual ICollection<UserFriendship> FriendRequestsAccepted { get; set; } } 

Update Cartography:

 modelBuilder.Entity<UserFriendship>() .HasRequired(i => i.User) .WithMany(i => i.FriendRequestsMade) .HasForeignKey(i => i.UserEntityId) .WillCascadeOnDelete(false); modelBuilder.Entity<UserFriendship>() .HasRequired(i => i.Friend) .WithMany(i => i.FriendRequestsAccepted) .HasForeignKey(i => i.FriendEntityId) .WillCascadeOnDelete(false); 

No migrations required.

To get all user friends:

 var someUser = ctx.UserEntity .Include(i => i.FriendRequestsMade.Select(x=> x.Friend)) .Include(i => i.FriendRequestsAccepted.Select(x => x.User)) .SingleOrDefault(i => i.UserEntityId == 1); 

Problem 2

Yes, you need to reassemble and delete all child objects. See my answer in this thread Net hierarchy update in Entity Framework

Following my answer, just create a UserFriendship dbset:

 public DbSet<UserFriendship> UserFriendships { get; set; } 

Now you can get all friends of a specific user ID, just delete all of them with one shot, and then delete the user.

Problem 3

Yes it is possible. You now have a dbset UserFriendship .

Hope this helps!

Answer 2

1) I don’t see a direct way to cascade many-to-many relationships using FluentApi.

2) The only available way that I can control is to use ManyToManyCascadeDeleteConvention , which I think is enabled by default, at least for me. I just checked one of my migrations, including many-to-many relationships, and indeed cascadeDelete: true exists for both keys.

EDIT: Sorry, I just found that ManyToManyCascadeDeleteConvention does not apply to the case of self-regulation. This answer says that

You receive this error message because in SQL Server a table cannot appear more than once in the list of all cascading relational actions that are started using the DELETE or UPDATE statement. For example, a cascading reference action tree should have only one path to a specific table in the cascading relational action tree.

So, you need to have your own delete code (for example, the sql command that you already have) and execute it in the transaction area .

3) You will not be able to access this table from the context. Typically, a table created by a many-to-many relationship is a byproduct of an implementation in a relational DBMS and is considered a weak table corresponding to its associated tables, which means that its rows must be cascaded-deleted if one of the related objects.

My advice is that firstly, check if your migration has changed the foreign keys of the table for cascading deletion. Then, if for some reason you need to limit the deletion of a record that has related records in a many-to-many relationship, then you just check it in your transactions.

4) To do this, if you really want to (FluentApi includes ManyToManyCascadeDeleteConvention by default), enclose the sql command and your SaveChanges in the transaction scope.