EF 4.0 - Many to Many relationship - problem with deletes

Question

My Entity Model is as follows: Person , Store and PersonStores Many-to-many child table to store PeronId,StoreId When I get a person as in the code below, and try to delete all the StoreLocations, it deletes them from PersonStores as mentioned but also deletes it from the Store Table which is undesirable. Also if I have another person who has the same store Id, then it fails saying "The DELETE statement conflicted with the REFERENCE constraint \"FK_PersonStores_StoreLocations\". The conflict occurred in database \"EFMapping2\", table \"dbo.PersonStores\", column 'StoreId'.\r\nThe statement has been terminated" as it was trying to delete the StoreId but that StoreId was used for another PeronId and hence exception thrown.

 Person p = null;
        using (ClassLibrary1.Entities context = new ClassLibrary1.Entities())
        {
            p = context.People.Where(x=> x.PersonId == 11).FirstOrDefault();
            List<StoreLocation> locations = p.StoreLocations.ToList();
            foreach (var item in locations)
            {
                context.Attach(item);
                context.DeleteObject(item);
                context.SaveChanges();
            }
        } 

Answer 1

The problem is that you don't actually want to delete the store itself, just the relation between the store and the person. Try something like this instead:

 Person p = null;
 using (ClassLibrary1.Entities context = new ClassLibrary1.Entities())
 {
     p = context.People.Where(x=> x.PersonId == 11).FirstOrDefault();
     p.StoreLocations.Clear();
     context.SaveChanges();
 }

That will get your person, remove all the stores from his list of stores, and save the changes. Note that you might need an include statement on the first row in the using block, depending on how your ObjectContext is configured.