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

.net c# entity-framework fluent-interface sql-server

Question

The ParentId in my table, which has a self reference, is an FK to the ID (PK).
I've established my relationship as follows using EF (code-first):

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

The DELETE commands do not work when I try to delete the children and their parent. EF attempts to destroy the parent record before making other changes to the database, which is not in the order I anticipated.

I am aware that I have two choices here, neither of which I prefer:

  1. Do a full save/commit first, then delete the parent record after the child records have been deleted. This is not a possibility due to the intricacy of my model and the logic that maintains it; I am unable to execute numerous commit commands at any time.
  2. Before erasing anything, end the connection. This appears to be a more practical option, however once more, I must make a second commit with a UPDATE statement before the DELETEs. I don't want to make several save/commit calls.
  3. Prior to deleting the parent record, use a trigger to remove the children. But I'd prefer to stay as far away from triggers and their negative effects as I can.

So, the question is: Is there a mechanism to make sure that child records are deleted before parent records? Is there a method to explicitly tell EF that these kids should come first before the parent? Maybe I'm missing it. Is it possible to instruct EF to delete records in descending order of IDs? I have no idea. Thoughts?

1
15
5/9/2013 2:13:23 PM

Popular Answer

Even if the response is a year old, I believe it is still insufficient. In my understanding, an arbitrary depth is represented by a self-referencing table.

Take the following structure, for instance:

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

How to remove Earth or Europe from the structure above is not addressed in the solution.

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

Add the following methods to the MyContext class:

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 use the following class to code-first populate the data:

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 store in my database using the code below:

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

then execute the deletes as follows:

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

    var deleteme = parent.Children.First();

    ctx.DeleteMyEntity(deleteme);
}

It causes my database to now be organized as follows:

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

where all of Europe's offspring are eliminated.

To show that you can use my code to recursively delete a node and all of its children from anywhere in the hierarchy, I have specified the first child of the root node in the example above.

You may easily change the line to read as follows if you want to try eliminating everything:

ctx.DeleteMyEntity(parent);

or any other desired node in the tree.

Obviously, I won't receive the prize, but I hope that my post will be useful to someone searching for a fix for self-referencing entities with any depth.

The complete source, which is a modified version of Slauma's code from the chosen response, is available here:

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();
        }
    }
}
18
8/6/2014 7:44:55 PM


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