主键约束更新实体框架中的多对多自引用表

TL;DR What is the proper way of rehydrating an entity framework object with a self referential many to many relationship from a DTO and updating it with the new values so that the database updates correctly?

将一个实体框架对象与一个DTO中的多个自引用关系重新水化，并使用新的值对其进行更新，从而使数据库正确更新，正确的方法是什么?

I have the following entity (irrelevant stuff trimmed)

我有以下实体(不相关的东西)

 public class Role
    {
        [Key]
        [Required]
        public String RoleId { get; set; }
        public List<Role> Children { get; set; }
    }

In my dbContext, I have set up a many to many relationship

在我的dbContext中，我建立了许多到许多的关系

     modelBuilder.Entity<Role>().HasMany(r => r.Children).WithMany();

I'm using MVC front end, with a web-api backend for an n-tier setup, and an mssql database.

我使用的是MVC前端，有一个用于n层设置的web api后端和一个mssql数据库。

The following chain of events happens

接下来发生的一系列事件

Browser->MVC Controller->REST call to Web API->WebAPI Controller->DB Context Query

浏览器->MVC控制器->REST调用到WebAPI ->WebAPI控制器->DB上下文查询

This chain happens twice, once to view the page in edit mode, and then again when the user pushes the save button to persist.

这个链发生了两次，一次在编辑模式下查看页面，然后当用户按下save按钮时再次进行。

When setting children on the entity, they always already exist first (IE, you don't create the parent and the children at the same time, you are just adding an existing child to a parent)

在实体上设置子元素时，它们总是先存在(例如，不同时创建父元素和子元素，而只是将现有的子元素添加到父元素中)

There is a DTO used by the MVC model and web API, which I re-hydrate to the entity on the web-api side.

MVC模型和web API使用了一个DTO，我将它重新注入到web API的实体上。

  public IHttpActionResult UpdateRoleInfo(RoleVM roleInfo){
     //lookup existing entity to update
     var existing = db.Roles.FirstOrDefault(y => y.RoleId == roleInfo.ExistingRoleId);

     ...Something happens here (see below for things i've tried)...

    db.SaveChanges();
}

My first try was this :

我的第一次尝试是:

 existing.Children = roleInfo.Children

This tried to recreate all of the existing children as part of the save. (Primary key constraint violation on the roles table)

这试图重建所有现有的孩子作为储蓄的一部分。(role表中的主键约束违反)

I changed that to

我改变了

  //Fetch all of the roles from the database to lookup the existing children
  var allRoles = GetRoles();
  //Have to reselect the roles from the DB so the DB doesn't try to recreate new ones for the children.
  var childrenToAdd = roleInfo.Roles.Select(role2 => allRoles.FirstOrDefault(r => r.RoleId == role2.RoleId)).ToList();
  existing.Children = childrenToAdd;

This correctly works for updating a role that does not already have any children, to add some the first time, but if you update a role that already has children, it tries to re-add the children to the database a second time, getting a primary key violation on the roles_role table

这个正确适用于更新角色,已经没有任何孩子,第一次添加一些,但如果你更新这个角色已经有了孩子,孩子们试图重新添加到数据库,获得主键违反roles_role桌上

I then tried pre-pending this code to the second one above,

然后我试着将这个代码预挂到上面的第二个，

                existing.Children.Clear();
                db.SaveChanges();

I would expect this to delete all the existing parent-child relationships from the many to many table for this parent, and then recreate them with the new children. Why not?

我希望这将删除所有现有的父-子关系，从这个父表的多个表中删除，然后使用新的子表重新创建它们。为什么不呢?

将一个实体框架对象与一个DTO中的多个自引用关系重新水化，并使用新的值对其进行更新，从而使数据库正确更新，正确的方法是什么?

2 个解决方案

#1

Try turning off auto detect changes (before retrieving from the DB) via

尝试关闭自动检测更改(在从DB检索之前)

context.Configuration.AutoDetectChangesEnabled = false;

Then set the state to modified on the specific role object you are updating

然后将状态设置为修改正在更新的特定角色对象

context.Entry(role).State = EntityState.Modified;

Haven't tried this myself on a self-referencing many-to-many table, but adding & updating entities in the manner can save all sorts of headaches where EF incorrectly infers what you are adding/updating

我还没有在一个自引用的多对多表上尝试过这种方法，但是以这种方式添加和更新实体可以避免EF错误地推断您正在添加/更新的内容的各种麻烦

#2

Found the problem.

发现这个问题。

On the initial load of the entity, I was using an include statement to eager load the children.

在实体的初始负载上，我使用了一个include语句来快速加载子项。

When I updated the entity, when I fetched it from the db again, I did not eager load the children. Therefore the additions/updates were getting confused. Once I put the include in during the upload Scenario #2 above worked (the explicit clear was not needed)

当我更新实体时，当我再次从db中获取实体时，我没有急切地加载子实体。因此添加/更新变得混乱。一旦我在上面的上载场景#2中加入了include(不需要明确的清除)

db.Roles.Include("Children").FirstOrDefault(z => z.RoleId == RoleId);

Also related, if you have this same problem when dealing with relationships across different tables, make sure all the entities that are involved in the graph are from the same DB context!

另外，如果在处理不同表之间的关系时遇到同样的问题，请确保图中涉及的所有实体都来自相同的DB上下文!

https://msdn.microsoft.com/en-us/magazine/dn166926.aspx

#1