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

Question

I have an entry removal problem with the EntityFramework and a many-to-many relationship for the same entity. 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");
    });

1. Am I correct, that it is not possible to define the Cascade Delete in Fluent API?

2. What is the best way to delete a UserEntity, for instance Foo?

   It looks for me now, I have to Clear the Foo's Friends Collection, then I have to load all other UserEntities, which contain Foo in Friends, and then remove Foo from each list, before I remove Foo from Users. But it sounds too complicateda.

3. Is it possible to access the relational table directly, so that I can remove entries like this

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

Thank you!

Update01:

4. My best solution for this problem for know is just to execute the raw sql statement before I call 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 failes for some reason, the FriendshipRelation are already removed and could not be rolled back. Or am I wrong?

Answer 1

Problem 1

The answer is quite simple:

Entity Framework cannot define cascade delete when it doesn't know which properties belong to the relationship.

In addition, in a many:many relationship there is a third table, that is responsible for managing the relationship. This table must have at least 2 FKs. You should configure the cascade delete for each FK, not for the "entire table".

The solution is create the FriendshipRelation entity. 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 have to change the UserEntity. Instead of a collection of UserEntity, it has a collection of UserFriendship. Like this:

public class UserEntity
{
    ...

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

Let's see the mapping:

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 retrieve all user's friends:

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

All of this works fine. However, there is a problem in that mapping (which also happens in your current mapping). Suppose that "I" am a UserEntity:

• I made a friend request to John -- John accepted
• I made a friend request to Ann -- Ann accepeted
• Richard made a friend request to me -- I accepted

When I retrieve my Friends property, it returns "John", "Ann", but not "Richard". Why? because Richard is the "maker" of the relationship not me. The Friends property is bound to only one side of the relationship.

Ok. How can I solve this? Easy! Change your 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 the Mapping:

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);

There are no migrations necessary.

To retrieve all user's 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 have to iterate the collection and remove all children objects. See my answer in this thread Cleanly updating a hierarchy in Entity Framework

Following my answer, just create a UserFriendship dbset:

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

Now you can retrieve all friends of a specific user id, just delete all of them in one shot, and then remove the user.

Problem 3

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

Hope it helps!

Answer 2

1) I don't see any straightforward way to control the cascade on the many-to-many relationships using FluentApi.

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

EDIT: Sorry, I just found that the ManyToManyCascadeDeleteConvention does not cover the self-referencing case. This related question's answer says that

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

So you end up having to have a custom delete code (like the sql command that you already have) and execute it in a transaction scope.

3) You should not be able to access that table from the context. Usually the table created by a many-to-many relationship is a by-product of the implementation in a relational DBMS and is considered a weak table respective to the related tables, which means that its rows should be cascade-deleted if one of the related entities is removed.

My advice is that, first, check if your migration is setting your table foreign keys to cascade delete. Then, if for some reason you need to restrict the deletion of a record which has related records in the many-to-many relationship, then you just check for it in your transactions.

4) In order to do that, if you really want to (FluentApi enables by default ManyToManyCascadeDeleteConvention), is to enclose the sql command and your SaveChanges in a transaction scope.