Exception with Entity Framework 6, disconnected context and many-to-many relationship

Question

I have a problem with EF6 when using "disconnected" contexts. I'm using database first and have modelled a many-to-many relationship this way (there is a junction table behind the relation shown, with two FKs, which together make up the composite PK for the junction table. No other columns in that junction table):

Since I want to use EF in a disconnected way (short-lived contexts, ready for a web API) I have also implemented the "painting the state" method from Julie Lerman and Rowan Millers book on the DbContext. Specifically the method they describe in chapter 4 of the book, "Recording original values" (p.102 and forward). This is my ApplyChanges method:

public static void ApplyChangesInGraph<TEntity>(TEntity root) where TEntity : class, IObjectWithState
    {
        using (var context = new NovaEntities2())
        {
            context.Set<TEntity>().Add(root);
            foreach (var entry in context.ChangeTracker.Entries<IObjectWithState>())
            {
                IObjectWithState stateInfo = entry.Entity;
                entry.State = ConvertState(stateInfo.State);
                if (stateInfo.State == State.Unchanged)
                {
                    ApplyPropertyChanges(entry.OriginalValues, stateInfo.OriginalValues);
                }
            }
            context.SaveChanges();
        }
    }

With this in place, I get an exception with the following test:

[Fact]
    public void ShouldNotResultInAnInsertOfPlaceOfEmployment()
    {
        ResetDbToKnownState();
        Employee employee;
        using (var context = new NovaEntities2())
        {
            employee = context.Employees
                //.Include(e => e.PlacesOfEmployment) // If enabled, an exception is thrown.
                .First();
        }
        employee.Name = "A new name";

        NovaEntities2.ApplyChangesInGraph(employee);
    }

If I enable the .Include above, the following exception occurs:

System.Data.Entity.Infrastructure.DbUpdateExceptionAn error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

InnerException: System.Data.SqlClient.SqlExceptionViolation of PRIMARY KEY constraint 'PK_EmployeePlaceOfEmployment'. Cannot insert duplicate key in object 'dbo.EmployeePlaceOfEmployment'. The duplicate key value is (140828, 14). Ie. with the .Include added above, EF issues an update of the Employee (fine) AND an insert of the already existing PlaceOfEmployment (not so fine) for the simple case above, where I only try to update the name of the Employee.

I cannot for the life of me figure out why an INSERT occurs here, violating the primary key. I've tried stepping through the foreach in the ApplyChanges method, and verified that all entity states are being set correctly. Indeed, on the first iteration, the Employee entity starts out as Added, and ends up in the Modified state. Next, the eager loaded PlaceOfEmployment entity is being processed and it starts out as Added and ends up in the Unchanged state. However, an INSERT is still being generated, resulting in the exception.

From SQL profiler:

Answer 1

I don't know this "Painting the state" method, but I'm not surprised that your example doesn't work.

With context.Set<TEntity>().Add(root) three things happen:

• The root (= employee) is in Added state
• All children (= employee.PlacesOfEmployment) are in Added state
• All relationship entries between root and children are in Added state

When you iterate over context.ChangeTracker.Entries you only fetch the entities from the context, but not the relationship entries (which are separate artifacts stored inside the context). In this loop you reset the state of root and children to Modified or Unchanged respectively. But this is only a change of that entity state. The relationship states are still Added and those states get translated into an INSERT into the "relationship table". That's why you get the duplicate key exception because the records to link root and children are already there.

Although the underlying ObjectContext allows to access relationship entries besides the entity state entries as well I doubt it will help because how could the ApplyChangesInGraph method recognize that the graph that is passed into the method has added or deleted or unchanged relationships if you only have one State property per entity?

Now, you could probably make your test method work if you use context.Set<TEntity>().Attach(root); instead of ...Add(root)... because this would put all entities and relationships into Unchanged state (or - I believe - it won't store any relationship entries in the context at all). As a result no INSERT into the link table (nor a DELETE from that table) should happen. However, I'm afraid, that with ...Attach the ApplyChangesInGraph method won't work then correctly anymore if you have an object graph with really new (or deleted) relationships where you actually expect an INSERT (or DELETE) for the link table.

Honestly, I have no clue how you could implement that generic method so that it works generally for all possible change scenarios. It seems to me that the object graph (with one State property per entity) just doesn't contain enough information to describe all relationship changes that could have happened while the graph was disconnected. (It would interest me how Julie Lerman and Rowan Miller are proposing to implement it.)