EF - Cascade Delete Not Working, Can't Delete Object

Question

I get this error:

System.Data.SqlClient.SqlException The DELETE statement conflicted with the REFERENCE constraint "FK_comments_postId__164452B1". The conflict occurred in database "awe", table "dbo.comments", column 'postId'. The statement has been terminated.

I have this structure:

    public class Post
    {
        public long Id { get; set; }
        public string Body { get; set; }     

        public long? ParentId { get; set; }
        public virtual Post Parent { get; set; }
        public virtual ICollection<Post> Posts { get; set; }

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

    public class Comment
    {
        public long Id { get; set; }
        public long PostId { get; set; }
        public virtual Post Post { get; set; }
        public string Body { get; set; }
    }

my delete method:

    public void Delete(long id)
    {
        var p = context.Set<Post>().Get(id);
        if(p == null) throw new MyEx("this post doesn't exist");
        if (p.Posts.Count > 0) throw new MyEx("this post has children and it cannot be  deleted");
        context.Set<Post>().Remove(p);
        context.SaveChanges();
    }

my DbContext:

public class Db : DbContext
{
    public DbSet<Post> Posts { get; set; }
    public DbSet<Comment> Comments { get; set; }
}

Answer 1

It sounds like the Post that you are trying to delete has child Comments.

Entity Framework will not take responsibility for cascading a delete in the database – it expects that you will achieve this by setting a cascading delete on the foreign key relationship in the RDBMS.

Having said this, if you delete a parent entity in Entity Framework, it will attempt to issue delete statements for any child entities which have been loaded into the current DbContext, but it will not initialize any child entities which have not yet been loaded. This may lead to the RDBMS throwing foreign key constraint violation exceptions if a cascading delete has not been specified, like the one you are seeing. For more details about how cascade delete “works” in Entity Framework, see this blog post (alas, dead link as of 2023-04-03).