Entity Framework Updating Many-To-Many Relationships - POCO

Question

I have following Many-To-Many relationship between two entities RelayConfig and StandardContact

Entities:

public class RelayConfig : EntityBase, IDataErrorInfo {
    ...
    //Associations
    public virtual ICollection<StandardContact> StandardContacts { get; set; }
}


public class StandardContact :EntityBase, IDataErrorInfo {
    ...
    //Associations
    public virtual ICollection<RelayConfig> RelayConfigs { get; set; }
}

Now I am trying to update RelayConfig and its relations with the StandardContact. Here is the code that updates RelayConfig.

public class RelayConfigRepository : GenericRepository<RelayConfig> {
    ....

    public void Update(RelayConfig relayConfig, List<StandardContact> addedContacts, List<StandardContact> deletedContacts) {
        context.RelayConfigs.Add(relayConfig);
        if (relayConfig.Id > 0) {
            context.Entry(relayConfig).State = EntityState.Modified;
        }

        addedContacts.ForEach(ad => relayConfig.StandardContacts.Add(ad));

        foreach (StandardContact standardContact in relayConfig.StandardContacts) {
            if (standardContact.Id > 0) {
                context.Entry(standardContact).State = EntityState.Modified;
            }
        }

        relayConfig.StandardContacts.ToList().ForEach(s => {
            if (deletedContacts.Any(ds => ds.Id == s.Id)) {
                context.Entry(s).State = EntityState.Deleted;
            }
        });
    }
    ...
}

When I run the update, I am getting exception, whose inner exception is given below.

InnerException: System.Data.SqlClient.SqlException
        Message=Violation of PRIMARY KEY constraint 'PK__Standard__EE33D91D1A14E395'. Cannot insert duplicate key in object 'dbo.StandardContactRelayConfigs'.

dbo.StandardContactRelayConfigs is the linking table that links RelayConfig and StandardContact. As you can see, the Update code changes all the entities to modified state if Id > 0 (except deleted records that are set in the end of the Update method).

I really cannot understand why entity framework is trying to insert row in the linked table and failing with the above exception. I already change the EntityState of existing RelayConfig.StandardContacts entities to Modified.

In short why do I get the exception pasted above.

regards, Nirvan.

Edit: The parameters to Update method above (addedContacts and deletedContacts) are already existing entities with Id > 0.

Edit2: As per your suggestions I removed the code for inserting fresh (not existing in database) records from the update method. So now my update method only adds existing StandardContact records to RelayConfig collection. But I still cannot get the code to work properly. First here is the code that I am using

    public void Update(RelayConfig relayConfig, List<StandardContact> addedContacts, List<StandardContact> deletedContacts) {
        context.RelayConfigs.Add(relayConfig);

        if (relayConfig.Id > 0) {
            context.Entry(relayConfig).State = EntityState.Modified;
        }


        addedContacts.ForEach(contact => {
            context.StandardContacts.Attach(contact);
            relayConfig.StandardContacts.Add(contact);
            objectContext.ObjectStateManager.
                ChangeRelationshipState(relayConfig, contact, rs => rs.StandardContacts, EntityState.Added);
        });
    }

For now I am just concentrating on added records. The above code works well when the StandardContact (contact variable) does not have any relationships with any other existing RelayConfig objects. In that case, a new entry is created in the junction table for each contact added to the RelayConfig.StandardContacts collection. But things get ugly (unpredictable behaviour) when the StandardContact (contact variable) is already in relationship with other RelayConfig objects. In that case, when the StandardContact is added to RelayConfig.StandardContacts Collection, the StandardContact also gets added in the database thus creating duplicate entry. Not only that, a new RelayConfig object is also created (I don't know from where) and inserted to the RelayConfigs table. I am really not able to comprehend the way entity framework works with Many-To-Many relationships.

@Ladislav, if you have some sample code that works on Many-To-Many relationship updates (for detached entities) then can I request you to please show me the same.

regards, Nirvan

Edit3 (Solution):

Eventually I ended up using a completely different approach. Here is the code for the Update

    public void Update(RelayConfig relayConfig, List<StandardContact> exposedContacts) {

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

        relayConfig.StandardContacts.Clear();
        exposedContacts.ForEach(exposedContact => {
            StandardContact exposedContactEntity = null;
            exposedContactEntity = context.StandardContacts.SingleOrDefault(sc => sc.Id == exposedContact.Id);
            if (exposedContactEntity != null) {
                relayConfig.StandardContacts.Add(exposedContactEntity);
            }
        });
    }

regards, Nirvan.

Answer 1

The problem is that many-to-many relation has its own state. So if you call this:

addedContacts.ForEach(ad => relayConfig.StandardContacts.Add(ad));

You tell EF that all added contacts are new relations which will be inserted to your junction table for many to many relation but calling this:

foreach (StandardContact standardContact in relayConfig.StandardContacts) {
    if (standardContact.Id > 0) {
        context.Entry(standardContact).State = EntityState.Modified;
    }
}

will change state of the contact entity but not the state of the relation - it is still tracked as new (btw. it cannot be modified but only added, deleted or unchanged). So when you save changes relations for all your contacts are added to junction table and if the same relation already exists in the database you will get exception (because junction table contains only two FKs which are also PK and in such case same relation = PK violation).

You also need to set state for relations by using:

var objectContext = ((IObjectContextAdapter)context).ObjectContext;
objectContext.ObjectStateManager.ChangeRelatioshipState(...);

But here comes the problem: you must differ between existing contacts which just created a new relation with existing or new relying config and also contacts which are completely new - I suggest you to handle completely new contacts separately otherwise your code will be very complex.