Deleting only one entry from Many-to-Many relationship

Question

Ok, so i have this problem right now. I have 2 Models like so:

public class Contact: BaseModel
    {

        public string LastName { get; set; }

        public string FirstMidName { get; set; }

        public string Adress { get; set; }
        public int UserID { get; set; }

        [Display(Name = "Full Name")]
        public string FullName
        {
            get
            {
                return FirstMidName+ " " + LastName;
            }
        }

        public virtual List<Group> Groups { get; set; }
        public virtual User User { get; set; }
        public virtual ICollection<Phones> Phones { get; set; }
    }

and also:

public class Group:BaseModel
    {
        public string Name { get; set; }
        public int UserID { get; set; }
        public virtual User User { get; set; }
        public virtual List<Contact> Contacts { get; set; }

    }

the idea is that many contacts can be in many groups. But here comes my problem. I remove the convention ondeleteCascade with many to many because i had to otherwise my code wouldnt work.But... how can i delete only one contact... without it cascading and deleting all the groups its in and all the contacts that group contains and so on and so forth. Thats my problem id love to be able to delete one contact not a group tho. i do not want the group to be deleted just the contact. Please help

Ivan Stoev · Accepted Answer · 2016-04-12T09:32:12.890

2

You should simply keep the cascade delete option turned on for that relationship.

In the many-to-many relationship, none of the sides "owns" the other. Instead, the relationship is maintained by a third entity (hidden in your case) called "link" table, so for instance deleting a contact will simple delete the group links tied to that contact, not the actual group entities. The same applies when deleting a group.

EDIT: While the above applies in general, it turns out that the actual problem is caused by the two one-to-many relationships (User->Contact and User->Group) not mentioned in the question which by default has cascade delete turned on. Which leads to a classical multiple cascade paths issue - when deleting a User record, the link table GroupContact records can be deleted by either User->Contact->GroupContact or User->Group->GroupContact, hence is a multiple delete path.

So you have to turn at least one of the User->Contact, User->Group or Contact<->Group relationship cascade delete off. Unfortunately that will cause you maintenance problems because you'll not be able to simple the delete one of the entities involved. Since I guess you are not deleting users so often, I would suggest you to turn User->Group relationship cascade delete off and manually delete the related User.Groups before deleting a User. Or put a delete trigger on the User table inside the database.

edited Apr 12 '16 at 09:32

answered Apr 12 '16 at 08:57

Ivan Stoev

195,425
15
312
343

I can`t .. It gives me an error when i update the database! Introducing FOREIGN KEY constraint 'FK_dbo.GroupContact_dbo.Contact_Contact_ID' on table 'GroupContact' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors. – stefantigro Apr 12 '16 at 08:59
That means you have something in common between `Contact` and `Group`. Can you post the `BaseModel` class? Or, looking at the classes, you probably have cascade delete from User to Contact, and User to Group? – Ivan Stoev Apr 12 '16 at 09:10
public class BaseModel { public int ID { get; set; } } It just holds an ID, And the whole idea of the BaseModel is for me to Pass it to the BaseRepository – stefantigro Apr 12 '16 at 09:11
That's set by default to `one-to-many` relationships which `User->Contact` and `User->Group` are. – Ivan Stoev Apr 12 '16 at 09:17
So you are saying that the fact that My group has a User it creates a problem? But that is a problem since the groups have to be connected to the user since only that user has access to his/her groups. And the user btw contains a List and a list – stefantigro Apr 12 '16 at 09:41
@stefantigro Correct. – Ivan Stoev Apr 12 '16 at 09:42
I think i am kind of beggining to understand the problem but i can`t really find the exact solution – stefantigro Apr 12 '16 at 09:44
What about The user? – stefantigro Apr 12 '16 at 09:44
All you are doing is correct. The problem is caused by limitation of SqlServer which is enforced in EF. Just turn cascade delete off as I suggested (`modelBuilder.Entity().HasRequired(e => e.User).WithMany(e => e.Groups).WillCascadeOnDelete(false);`. Then, when deleting an user, do first `db.Groups.RemoveRange(db.Groups.Where(g => g.UserId == userId_to_be_deleted));`. – Ivan Stoev Apr 12 '16 at 09:52

score 1 · Answer 2 · edited May 23 '17 at 11:59

1

You need to think about how your data is structured. Why does each contact have to hold a list of all groups? By doing this, each contact in your system holds all information about each other contact, including himself.

This is SQL's way of telling you your data relationship doesn't make sense.

Why not rather have each group hold a list of all contacts in its group?

That's your first option, but if you REALLY need to make this work, probably you want to build a mapping table. Lots of examples of these around.

edited May 23 '17 at 11:59

Community

1
1

answered Apr 12 '16 at 09:03

S.V.

1,181
6
23

That is an alternative but i`m required to use many-to-many relationship... and besides.. There has to be a way to delete just a single entity? I can write you the sql statement that can do that. Surely entity framework can do the same? – stefantigro Apr 12 '16 at 09:06
So i should remove the List and List ? And then do the mapping table? – stefantigro Apr 12 '16 at 09:15
the mapping table does not solve my problem. it still gives an error with the FK – stefantigro Apr 12 '16 at 09:31

Deleting only one entry from Many-to-Many relationship

2 Answers2

Linked