Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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?

share|improve this question
1  
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. – Brad Gardner May 9 at 14:19
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. – Kon May 9 at 14:22
Update: Unable to add cascade on delete to an FK within a self reference in a table, because of the cycle error. – Kon May 9 at 21:33
Can you utilize a Stored Procedure ? – Max May 16 at 8:55
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. – Kon May 16 at 12:39
add comment (requires an account with 50 reputation)

2 Answers

up vote 4 down vote
+50

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:

screen 1

Screenshot from SQL profiler after the last SaveChanges:

screen 2

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

share|improve this answer
add comment (requires an account with 50 reputation)
up vote 0 down vote

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.

share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.