How to Clear() all elements from Entity Framework ICollection?

Question

I have problems removing all elements from a collection in entity framework using Clear()

Consider the often used example with Blogs and Posts.

public class Blog
{
    public int Id {get; set;}
    public string Name {get; set;}
    public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }

    // foreign key to Blog:
    public int BlogId { get; set; } 
    public virtual Blog Blog { get; set; }

    public string Title { get; set; }
    public string Text { get; set; }
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs {get; set;}
    public DbSet<Post> Posts {get; set;}
}

A Blog has many Posts. A Blog has an ICollection of Posts. There is a straightforward one-to-many relation between Blogs and Posts.

Suppose I want to remove all Posts from a Blog

Of course I could do the following:

Blog myBlog = ...
var postsToRemove = dbContext.Posts.Where(post => post.BlogId == myBlog.Id);
dbContext.RemoveRange(postsToRemove);
dbContext.SaveChanges();

However, the following seems easier:

Blog myBlog = ...
myBlog.Posts.Clear();
dbContext.SaveChanges();

However this leads to an InvalidOperationException:

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

What is the proper way to clear the collection? Is there a fluent API statement for this?

As far as I know, there's unfortunately no way other than using *.Remove()* or *.RemoveRange()*. Other than running SQL directly via EF, of course, but that misses the point, I think. — Rob, Oct 05 '16 at 08:06
Your case is described here: http://stackoverflow.com/a/6181060/6804888. Seems that without changing the model, you have to manually delete the entities after removing them from collection. — kiziu, Oct 05 '16 at 08:11

score 20 · Accepted Answer · answered Oct 05 '16 at 08:13

There is a difference between your two code samples.

Your first code sample dbContext.RemoveRange(postsToRemove) removes the Post records. Therefor, any relationship involving these records are also removed.

In your second code sample myBlog.Posts.Clear() you are removing the relationship between myBlog and its corresponding Post records. The 'real' underlying action is to set the value of BlogId of the Post records to null. Unfortunately this is not possible, since BlogId is set to not-nullable. So, in short, the relationship is removed, and no records are actually deleted.

You are right.I understand why they choose this method. There are examples where deleting the items when the collection is cleared is undesirable. — Harald Coppoolse, Oct 05 '16 at 09:28

bubi · Answer 2 · 2016-10-05T09:07:05.657

9

Clear works on the relationship and not on deleting the entity.

There is a working (I think more readable) half way solution between the two solutions you wrote.

dbContext.Posts.RemoveRange(myBlog.Posts);
// Now (also before SaveChanges) the myBlog.Posts is empty
dbContext.SaveChanges();

EDIT
RemoveRange also removes Posts from the Blog.Posts collection

edited Oct 05 '16 at 09:07

answered Oct 05 '16 at 08:52

bubi

6,414
3
28
45

If you really want to clear the collection and remove all elements (like in the question, this solution is the best readable. However if you only have the id of the blog whose posts you want to create the original method saves retrieving the Blog first – Harald Coppoolse Oct 05 '16 at 09:25

How to Clear() all elements from Entity Framework ICollection?

2 Answers2

Linked