Remove from collection in Entity Framework 6

Question

Addition (1): One of the replies said that it ought to work. So I added the complete exception at the end of this post

Simplified: I have a sequence of objects, each with a collection of other objects. An example of this is a blog with a collection of posts. MSDN uses this quite often as an example

MSDN Code First to a new DataBase

What I see is that if I create a blog, I can add some posts, add the blog to the database and call SaveChanges. The entity framework recognizes that the posts ought to be in a different table with a foreign key to the table of blogs.

This is exactly how one would have created the database if Entity Framework wasn't around.

Getting all Posts from a Blog and adding a Post can be done without any knowledge about the separate Post table and the foreign key to the Blog table.

However, suddenly when I try to remove a post from a blog I get errors about foreign keys.

Note: the following is not about efficiency

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; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public virtual Blog Blog { get; set; } 
}

public class BloggingContext : DbContext
{
    public BloggingContext(string nameOrConnectionString)
        : base(nameOrConnectionString){}

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

Usage would be as follows:

class Program
{
    static void Main(string[] args)
    {
        const string dbName = "MyTestDb";
        Database.SetInitializer(new DropCreateDatabaseAlways <BloggingContext> ());
        using (var context = new BloggingContext(this.DbName))
        {
            // create a blog:
            var blog = new Blog()
            {
                Name = "First Blog",
                Posts = new List<Post>()
                {
                    new Post() { Title = "My 1st Post", Content = "Hello World!" },
                    new Post() { Title = "My 2nd Post", Content = "All animals are equal but pigs are more equal"},
                    new Post() { Title = "My 3rd Post", Content = "Shall I compare thee to a summer's day" },
                 },
            };
            context.Blogs.Add(blog);
            context.SaveChanges();

The nice thing is, that the client doesn't have to know how the database is actually organized. The client doesn't have to know that the Blog and Post are in different tables. For the client a blog has a collection of Posts.

Likewise, if a client asks for a blog and accesses the posts in the blog, Entity Framework knows where to get the posts. The client doesn't have to be aware that the Posts are saved in a different table with a foreign key.

blog = context.Blogs.First();
var lastPost = blog.Posts.Last();

Entity Framework is even thus smart that it doesn't select items that are not needed. If I wouldn't use a Post collection, the Posts wouldn't be retrieved from the database

Therefore I had expected that the following would work:

blog.Posts.Remove(lastPost);
context.AddOrUpdate(blog);
context.SaveChanges();

I had expected that entity framework would have known that the last post was removed, and thus would order to remove the item from the table of Posts. Yet it doesn't do this. I get an exception that "The relationship could not be changed because one or more of the foreign-key properties is non-nullable. bla bla", meaning that the Post ought to have been removed from the table of Posts.

Question: Is it correct that a client doesn't have to know the database model Entity Framework created, except when the data is to be removed?

Addition: Of course I can remove the item from the DbSet, but my question is: If Entity Framework is smart enough the I don't have to add the post to the DbSet, why should I have to remove it?

Someone asked for the exception text

System.InvalidOperationException was unhandled

• _HResult=-2146233079

• _message=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.

• HResult=-2146233079

• IsTransient=false
• Message=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.
• Source=EntityFramework

StackTrace: at System.Data.Entity.Core.Objects.ObjectContext.PrepareToSaveChanges(SaveOptions options) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options) at System.Data.Entity.Internal.InternalContext.SaveChanges() at System.Data.Entity.Internal.LazyInternalContext.SaveChanges() at System.Data.Entity.DbContext.SaveChanges() at TryInMemoryDbSet.TestDirectDbContext.Test2() in c:\Users\Harald\Documents\Visual Studio 2013\Projects\EntityFramework\TryInMemoryDbSet\TryInMemoryDbSet\TestDirectDbContext.cs:line 75 at TryInMemoryDbSet.Program.Main(String[] args) in c:\Users\Harald\Documents\Visual Studio 2013\Projects\EntityFramework\TryInMemoryDbSet\TryInMemoryDbSet\Program.cs:line 19 InnerException:

Answer 1

Question: Is it correct that a client doesn't have to know the database model Entity Framework created, except when the data is to be removed?

Addition: Of course I can remove the item from the DbSet, but my question is: If Entity Framework is smart enough the I don't have to add the post to the DbSet, why should I have to remove it?

Anwser:

You should remove it from the DbSet because when you remove it from the collection, you are actually removing the "relationship" between them. However, the relationship (in your case) is mandatory, the Post's BlogId property is NOT NULL, it means that all post must belong to a blog. When you remove from the collection, EF is trying to set the FK as a null value, that's why you get the exception.

That is the generated SQL that EF executes when you remove a Post from a Blog

UPDATE [dbo].[Posts]
SET [BlogId] = NULL
WHERE ([Id] = @0)

It would work if BlogId was nullable.

Answer 2

blog.Posts.Remove(lastPost);

Remove lastPost from the blog.Posts collection, not from the database.

As I imagine for a Post, a blog is required, the Post can't exist with a PostId valued to null.

At least 2 solutions:

• context.Set().Remove(lastPost), or
• use the BlogId in a composite PK for the Post. So when set to null PostId will trigger the delete in the Db.