The DELETE statement conflicted with the SAME TABLE REFERENCE constraint with Entity Framework

Question

I have a table with a self reference where the ParentId is an FK to the ID (PK).
Using EF (code-first), I've set up my relationship as follows:

this.HasOptional(t => t.ParentValue)
    .WithMany(t => t.ChildValues)
    .HasForeignKey(t => t.ParentId);

When I try to delete the children and its parent, the DELETE commands EF issues to the database are not in the order I expected them to go - it attempts to delete the parent record first.

I realize that I have a couple of options here (neither of which I like):

1. Delete child records first, do a full save/commit, and then delete parent record. With the complexity of my model and the logic that maintains it, this isn't an option - can't issue multiple commit commands whenever I want.
2. Dissolve the relationship before deleting anything. This seems like a more sensible solution, but again, I have to issue separate commit with an UPDATE statement before the DELETEs. I want to avoid multiple save/commit calls.
3. Use a trigger to delete children prior to deleting parent record. But I'd like to avoid triggers and their problematic nature as much as possible.

So the question is.. is there a way to enforce the deletion of children before the parent record? Perhaps I'm missing some sort of an explicit way of telling EF that it needs to take care of these children before the parent? Maybe there's a way to direct EF to delete in a descending order of IDs? I don't know.. thoughts?

Yielding

Can you utilize a cascade delete here? If you are using code first migrations, you can set the cascade=true when the table is created, otherwise you may have to update it through another migration or via the database. I would think EF would handle it if it was able to cascade. – Inward

Good idea. Our DBAs don't want to enable cascade for the whole DB, but we may be able to do it just on this FK reference. It's what I was just discussing with a DBA. :) Though I'm still looking for other suggestions/ideas perhaps within the scope of EF-related code. – Yielding

Update: Unable to add cascade on delete to an FK within a self reference in a table, because of the cycle error. – Yielding

Can you utilize a Stored Procedure ? – Headboard

Well the whole point of an ORM like EF (besides the relationship mappings and entity dependencies and all that good stuff) is that you deal with your entity models and not have to write your own SQL statements. – Yielding

Answer 1

I realize the answer is a year old, but I find it incomplete. In my mind, a self-referencing table is used to represent an arbitrary depth.

For example, consider the following structure:

/*  
 *  earth
 *      europe
 *          germany
 *          ireland
 *              belfast
 *              dublin
 *      south america
 *          brazil
 *              rio de janeiro
 *          chile
 *          argentina                 
 *               
 */

The answer does not solve how to delete earth, or europe, from the structure above.

I submit the following code as an alternative (modification of answer provided by Slauma, who did a good job btw).

In the MyContext class, add the following methods:

public void DeleteMyEntity(MyEntity entity)
{
    var target = MyEntities
        .Include(x => x.Children)
        .FirstOrDefault(x => x.Id == entity.Id);

    RecursiveDelete(target);

    SaveChanges();

}

private void RecursiveDelete(MyEntity parent)
{
    if (parent.Children != null)
    {
        var children = MyEntities
            .Include(x => x.Children)
            .Where(x => x.ParentId == parent.Id);

        foreach (var child in children)
        {
            RecursiveDelete(child);
        }
    }

    MyEntities.Remove(parent);
}

I populate the data using code-first with the following class:

public class TestObjectGraph
{
    public MyEntity RootEntity()
    {
        var root = new MyEntity
        {
            Name = "Earth",
            Children =
                new List<MyEntity>
                    {
                        new MyEntity
                        {
                            Name = "Europe",
                            Children =
                                new List<MyEntity>
                                {
                                    new MyEntity {Name = "Germany"},
                                    new MyEntity
                                    {
                                        Name = "Ireland",
                                        Children =
                                            new List<MyEntity>
                                            {
                                                new MyEntity {Name = "Dublin"},
                                                new MyEntity {Name = "Belfast"}
                                            }
                                    }
                                }
                        },
                        new MyEntity
                        {
                            Name = "South America",
                            Children =
                                new List<MyEntity>
                                {
                                    new MyEntity
                                    {
                                        Name = "Brazil",
                                        Children = new List<MyEntity>
                                        {
                                            new MyEntity {Name = "Rio de Janeiro"}
                                        }
                                    },
                                    new MyEntity {Name = "Chile"},
                                    new MyEntity {Name = "Argentina"}
                                }
                        }
                    }
        };

        return root;
    }
}

which I save to my database with the following code:

ctx.MyEntities.Add(new TestObjectGraph().RootEntity());

then invoke the deletes like so:

using (var ctx = new MyContext())
{
    var parent = ctx.MyEntities
        .Include(e => e.Children)
        .FirstOrDefault();

    var deleteme = parent.Children.First();

    ctx.DeleteMyEntity(deleteme);
}

which results in my database now having a structure like so:

 /*  
 *  earth
 *      south america
 *          brazil
 *              rio de janeiro
 *          chile
 *          argentina                 
 *               
 */

where europe and all of its children are deleted.

in the above, I am specifying the first child of the root node, to demonstrate that using my code you can recursively delete a node and all of its children from anywhere in the hierarchy.

if you want to test deleting everyting, you can simply modify the line like this:

ctx.DeleteMyEntity(parent);

or whichever node you want in the tree.

obviously, I won't get the bounty, but hopefully my post will help someone looking for a solution that works for self-referencing entities of arbitrary depth.

Here is the full source, which is a modified version of Slauma's code from the selected answer:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace EFSelfReference
{
    public class MyEntity
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public int? ParentId { get; set; }
        public MyEntity Parent { get; set; }

        public ICollection<MyEntity> Children { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<MyEntity> MyEntities { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MyEntity>()
                .HasOptional(e => e.Parent)
                .WithMany(e => e.Children)
                .HasForeignKey(e => e.ParentId);
        }


        public void DeleteMyEntity(MyEntity entity)
        {
            var target = MyEntities
                .Include(x => x.Children)
                .FirstOrDefault(x => x.Id == entity.Id);

            RecursiveDelete(target);

            SaveChanges();

        }

        private void RecursiveDelete(MyEntity parent)
        {
            if (parent.Children != null)
            {
                var children = MyEntities
                    .Include(x => x.Children)
                    .Where(x => x.ParentId == parent.Id);

                foreach (var child in children)
                {
                    RecursiveDelete(child);
                }
            }

            MyEntities.Remove(parent);
        }
    }

    public class TestObjectGraph
    {
        public MyEntity RootEntity()
        {
            var root = new MyEntity
            {
                Name = "Earth",
                Children =
                    new List<MyEntity>
                    {
                        new MyEntity
                        {
                            Name = "Europe",
                            Children =
                                new List<MyEntity>
                                {
                                    new MyEntity {Name = "Germany"},
                                    new MyEntity
                                    {
                                        Name = "Ireland",
                                        Children =
                                            new List<MyEntity>
                                            {
                                                new MyEntity {Name = "Dublin"},
                                                new MyEntity {Name = "Belfast"}
                                            }
                                    }
                                }
                        },
                        new MyEntity
                        {
                            Name = "South America",
                            Children =
                                new List<MyEntity>
                                {
                                    new MyEntity
                                    {
                                        Name = "Brazil",
                                        Children = new List<MyEntity>
                                        {
                                            new MyEntity {Name = "Rio de Janeiro"}
                                        }
                                    },
                                    new MyEntity {Name = "Chile"},
                                    new MyEntity {Name = "Argentina"}
                                }
                        }
                    }
            };

            return root;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer<MyContext>(
               new DropCreateDatabaseAlways<MyContext>());
            using (var ctx = new MyContext())
            {
                ctx.Database.Initialize(false);

                ctx.MyEntities.Add(new TestObjectGraph().RootEntity());
                ctx.SaveChanges();
            }

            using (var ctx = new MyContext())
            {
                var parent = ctx.MyEntities
                    .Include(e => e.Children)
                    .FirstOrDefault();

                var deleteme = parent.Children.First();

                ctx.DeleteMyEntity(deleteme);
            }

            Console.WriteLine("Completed....");
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

Selfsustaining

Unfortunately, this causes multiple roundtrips to the database for each layer of the children. Checked it with SQL Profiler. – Anselmo

Answer 2

Deleting parent and child like the following does work for me. The children are deleted before the parent and it is a single database roundtrip (one call to SaveChanges) with of course three DELETE statements in a single transaction:

using (var ctx = new MyContext())
{
    var parent = ctx.MyEntities.Include(e => e.Children).FirstOrDefault();

    foreach (var child in parent.Children.ToList())
        ctx.MyEntities.Remove(child);

    ctx.MyEntities.Remove(parent);

    ctx.SaveChanges();
}

(Using ToList() is necessary here because calling Remove for the children also removes from the parent's Children collection. Without using ToList a runtime exception would be thrown that the collection the foreach loop is iterating over has been modified.)

The order in which Remove is called for children and parent doesn't matter. This works as well:

using (var ctx = new MyContext())
{
    var parent = ctx.MyEntities.Include(e => e.Children).FirstOrDefault();

    var children = parent.Children.ToList();

    ctx.MyEntities.Remove(parent);

    foreach (var child in children)
        ctx.MyEntities.Remove(child);

    ctx.SaveChanges();
}

EF sorts the DELETE statements in the correct order in both cases.

Full test program (EF 5 / .NET 4.5 / SQL Server):

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace EFSelfReference
{
    public class MyEntity
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public int? ParentId { get; set; }
        public MyEntity Parent { get; set; }

        public ICollection<MyEntity> Children { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<MyEntity> MyEntities { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<MyEntity>()
                .HasOptional(e => e.Parent)
                .WithMany(e => e.Children)
                .HasForeignKey(e => e.ParentId);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer<MyContext>(
                new DropCreateDatabaseAlways<MyContext>());
            using (var ctx = new MyContext())
            {
                ctx.Database.Initialize(false);

                var parent = new MyEntity { Name = "Parent",
                    Children = new List<MyEntity>() };

                parent.Children.Add(new MyEntity { Name = "Child 1" });
                parent.Children.Add(new MyEntity { Name = "Child 2" });

                ctx.MyEntities.Add(parent);

                ctx.SaveChanges();
            }

            using (var ctx = new MyContext())
            {
                var parent = ctx.MyEntities.Include(e => e.Children)
                    .FirstOrDefault();

                foreach (var child in parent.Children.ToList())
                    ctx.MyEntities.Remove(child);

                ctx.MyEntities.Remove(parent);

                ctx.SaveChanges();
            }
        }
    }
}

Screenshot after the first using block with current content in DB table before the entities are deleted:

Screenshot from SQL profiler after the last SaveChanges:

I.e. Child 1 (Id = 2) and Child 2 (Id = 3) are deleted before Parent (Id = 1).

Zingg

Answer 3

There is another way, (think about the draw backs before doing it ... ) you can set the relationship to be ON DELETE CASCADE, and try to delete just the parent row.

Shaunteshave

You can't delete cascade on a SAME TABLE REFERENCE constraint, as described in the title of the question. – Mckenzie