Cascading delete with Entity Framework

Question

I have a comments table which contains amongst other things the columns Id - the ID of the comment, Commenter_Id the ID of the user who posted it, and ParentComment_Id a self referencing foreign key. There are only two levels to the commenting, parents and sub comments. If a comment record has a ParentComment_Id of null, then it is a parent comment.

I'm trying to write an expression to delete all the comments for a user, but this is causing problems because of that self reference I mentioned earlier.

Take this records sample as an example of the problem:

User with ID 2 posted a comment, with an ID of 3. Because this was the parent comment it has a ParentComment_Id value of null. Later on, User with ID 1 responds to that comment with a sub-comment, creating comment 7 (there were other comments/subcomments between these two hence the Id increment jump).

I'm not able to delete Comment ID 3 because the sub comment, Comment ID 7, has a foreign key to it.

Currently my Entity Framework statement for trying to delete comments is as follows:

context.Comments.Where(x => x.Commenter.Id == user.Id).Delete();

But this gives me an exception because of the described problem.

I could probably fix this using a few foreach loops, but I was hoping there is an easier way like context.Cascade().Where(.... For those wondering the Delete() method is part of the EntityFramework.Extended package.

If a comment is deleted, do you want all the child comments to be deleted as well, or do you want their ParentComment_Id sent to null? — jjj
– jjj, Commented Jun 3, 2015 at 4:55

ocuenca · Accepted Answer · 2015-06-03 13:46:54Z

3

If you have an entity like this:

public class Comment
{
  public int Id{get;set;}
  public int? ParentCommentId{get;set;}

  public virtual Comment ParentComment{get;set;}
  public virtual ICollection<Comment> Comments{get;set;}
}

You could try with this configuration:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Comment>()
                .HasOptional(c=>c.ParentComment)
                .WithMany(c=>c.Comments)
                .HasForeignKey(c => c.ParentCommentId) 
                .WillCascadeOnDelete(true);
}

You can configure cascade delete on a relationship by using the WillCascadeOnDelete method.If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null.

edited Jun 3, 2015 at 13:46

answered Jun 2, 2015 at 22:55

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TYY · Accepted Answer · 2015-06-02 22:43:16Z

1

You can try adding this to your dbcontext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Comments>()
                .HasForeignKey(d => d.ParentCommentId) 
                .WillCascadeOnDelete(true);
}

edited Jun 2, 2015 at 22:43

answered Jun 2, 2015 at 22:41

TYY

2,7161 gold badge16 silver badges14 bronze badges

3 Comments

Frayt Over a year ago

This seems good, is there a way to set all entities to cascade without repeating the statement n times?

TYY Over a year ago

You might be able to add modelBuilder.Conventions.Add<OneToManyCascadeDeleteConvention>() modelBuilder.Conventions.Add<ManyToManyCascadeDeleteConvention>(). Haven't tried it though.

Frayt Over a year ago

I'm doing this: modelBuilder.Entity<Comment>().WillCascadeOnDelete(true); with a DbSet of public DbSet<Comment> Comments{ get; set; } however it's not finding your WillCascadeOnDelete. I'm on EF 6

Collectives™ on Stack Overflow

Cascading delete with Entity Framework

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related