EntityFramework CodeFirst:同一张表多对多关系的级联删除 [英] EntityFramework CodeFirst: CASCADE DELETE for same table many-to-many relationship

问题描述

我遇到了 EntityFramework 的条目删除问题以及同一实体的多对多关系.考虑这个简单的例子:

I have an entry removal problem with the EntityFramework and a many-to-many relationship for the same entity. Consider this simple example:

实体:

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

Fluent API 配置:

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

1. 我是否正确，不能在 Fluent API 中定义 Cascade Delete?

2. 删除 UserEntity 的最佳方法是什么，例如 Foo?

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?

它现在正在寻找我，我必须 Clear Foo 的 Friends 集合，然后我必须加载所有其他 UserEntities，在 Friends 中包含 Foo，然后从每个列表中删除 Foo，然后再删除 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.

是否可以直接访问关系表，这样我就可以删除这样的条目

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

谢谢！

Update01:

1. 对于这个问题，我最好的解决方案是在调用 SaveChanges 之前执行原始 sql 语句:

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

但这样做的缺点是，如果 SaveChanges 由于某种原因失败，则 FriendshipRelation 已经被删除并且无法回滚.还是我错了?

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?

推荐答案

问题1

答案很简单:

实体框架在不知道哪些属性属于关系时无法定义级联删除.

此外，在多对多关系中还有第三个表，负责管理关系.该表必须至少有 2 个 FK.您应该为每个 FK 配置级联删除，而不是为整个表"配置.

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".

解决方案是创建 FriendshipRelation 实体.像这样:

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
}

现在，您必须更改 UserEntity.它不是 UserEntity 的集合，而是 UserFriendship 的集合.像这样:

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

让我们看看映射:

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

生成的迁移:

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

检索所有用户的朋友:

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

所有这些都可以正常工作.但是，该映射存在问题(这也发生在您当前的映射中).假设我"是一个UserEntity:

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:

• 我向 John 提出了好友请求 -- John 接受了
• 我向 Ann 提出了好友请求 -- Ann 接受了
• Richard 向我提出了一个好友请求 -- 我接受了

当我检索我的 Friends 属性时，它返回John"、Ann"，但不返回Richard".为什么?因为理查德是关系的制造者"而不是我.Friends 属性只绑定到关系的一侧.

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.

好的.我该如何解决这个问题?简单！更改您的 UserEntity 类:

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

更新映射:

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

不需要迁移.

检索所有用户的朋友:

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

问题 2

是的，您必须迭代集合并删除所有子对象.在这个线程中查看我的答案在实体框架中干净地更新层次结构

按照我的回答，只需创建一个 UserFriendship 数据库集:

Following my answer, just create a UserFriendship dbset:

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

现在您可以检索特定用户ID的所有朋友，只需一次性删除所有朋友，然后删除该用户.

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

是的，这是可能的.您现在有一个 UserFriendship 数据库集.

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

希望对你有帮助！

这篇关于EntityFramework CodeFirst:同一张表多对多关系的级联删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！