primary key constraint updating many-to-many self referential table in entity framework code-first

asp.net-mvc asp.net-web-api entity-framework entity-framework-6 sql-server

Question

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?

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

     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.

The following chain of events happens

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

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

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.

  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)

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

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?

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?

1
2
4/24/2015 5:20:37 PM

Accepted Answer

Found the problem.

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

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

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

0
4/24/2015 7:02:47 PM

Popular Answer

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

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow