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!