Delete row in table with self-referencing many-to-many relationship in Entity Framework Code First

asp.net-mvc ef-code-first entity-framework-6 many-to-many

Question

I've been scratching my head and reading tons of posts but couldn't figure this out.

I have a Person table (see following). Entities in that table can have Children and Parents, who also are in the same table.

public class Person
{
    public int ID { get; set; }
    public int Name { get; set; }
    public virtual ICollection<Person> Children { get; set; }
    public virtual ICollection<Person> Parents { get; set; }
}

My model builder in DataContext is like this :

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .HasMany(c => c.Children)
            .WithMany(p => p.Parents)
            .Map(m=>
                {
                    m.ToTable("ParentChild");
                    m.MapLeftKey("ParentID");
                    m.MapRightKey("ChildID");
                });
    }

This works fine and automatically creates a table named ParentChild with ParentID and ChildID.

Now the thing is, I want to delete a Person who has children, but without deleting the children. So I guess I need to also delete the corresponding line in my ParentChild table (which is auto-generated), but don't know how to do that.

Note : I've also tried to explicitly create the ParentChild table, but this doesn't work, it's rejected when doing add-migration.

To give you more information, here is the error I get when I try to delete one of the row in Person :

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.PersonPerson_dbo.Person_Person_ID". The conflict occurred in database "MyDatabase", table "dbo.ParentChild", column 'ParentID'.

(By the way, I don't know why it talks about a PersonPerson database since I give my auto-generated relation database the name ParentChild. But even if I don't give it a name, it doesn't work).

Last thing, here's the Remove() stub in PersonController (I left it as it was originally made by Scaffolding) :

public ActionResult Delete(int id)
    {
        try
        {
            Person person = db.Persons.Find(id);
            db.Persons.Remove(person);
            db.SaveChanges();
        }
        catch (RetryLimitExceededException dex)
        {
            return RedirectToAction("Delete", new { id = id, saveChangesError = true });
        }
        return RedirectToAction("Index");
    }

I hope someone can help me!

Thanks in advance for your help and cheers,

Marius

1
0
11/21/2014 7:29:38 AM

Accepted Answer

OK, I finally managed to figure this out.

The solution is modifying the Delete() stub in PersonController, like this :

using (var context = new DataContext())
            {
                Person person= context.Persons.Find(id);

                foreach (var child in person.Children.ToList())
                {
                    person.Children.Remove(child);
                }

                foreach (var parent in person.Parents.ToList())
                {
                    person.Parents.Remove(parent);
                }

                context.Persons.Remove(patient);

                context.SaveChanges();
            }

This way, the Delete() method deletes references to Parents and Children, and reference only. And as it does it at the same time when I call SaveChanges, it avoids trouble with ForeignKey constraints!

Hope this helps someone.

Cheers!

2
11/16/2014 10:18:00 AM


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