EF Core error when updating nested list of an object

The problem

In one of our projects that uses EF Core, we have a one-to-many relationship model like this

    public class Blog
    {
        [Key]
        public Guid BlogId { get; set; }
        public string Name { get; set; }

        public ICollection<Post> Posts { get; set; } = new List<Post>();
    }

    public class Post
    {
        [Key]
        public Guid PostId { get; set; }
        public string Title { get; set; }

        public Guid BlogId { get; set; }
        public Blog Blog { get; set; }
    }

We need to build a new endpoint which allows users to update the blog as well as all its posts. When I review the pull request from my team member, the way he builds it is that he would remove all existing posts and add all the new ones.

        public class BlogDto
        {
            public Guid Id { get; set; }
            public string Name { get; set; }

            public IEnumerable<PostDto> Posts { get; set; } = new List<PostDto>();
        }

        public class PostDto { 
            public string Title { get; set; }
        }


        [HttpPost]
        public IActionResult Post(BlogDto updatedBlog)
        {
            var blog = _bloggingContext.Blogs
                .Include(x => x.Posts)
                .FirstOrDefault(x => x.BlogId == updatedBlog.Id);

            _bloggingContext.RemoveRange(blog.Posts);

            _bloggingContext.AddRange(updatedBlog.Posts.Select(x => new Post
            {
                BlogId = blog.BlogId,
                PostId = Guid.NewGuid(),
                Title = x.Title
            }));

            _bloggingContext.SaveChanges();

            blog.Name = updatedBlog.Name;

            _bloggingContext.Update(blog);
            _bloggingContext.SaveChanges();

            return NoContent();
        }

That code works but it is lengthy and it will make multiple trips to the database because of the multiple SaveChanges().

I’m very sure it would also work if you directly set the list of Posts to a new list and EF would work itself out.

        [HttpPost]
        public IActionResult Post(BlogDto updatedBlog)
        {
            var blog = _bloggingContext.Blogs
                .Include(x => x.Posts)
                .FirstOrDefault(x => x.BlogId == updatedBlog.Id);

            blog.Name = updatedBlog.Name;
            blog.Posts = updatedBlog.Posts.Select(x => new Post
            {
                PostId = Guid.NewGuid(),
                Title = x.Title
            }).ToList();

            _bloggingContext.Update(blog);
            _bloggingContext.SaveChanges();

            return NoContent();
        }

However, we get this exception when we run the above code

An unhandled exception has occurred while executing the request.
      Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

When I enable logging to see the generated SQL query, this is what I get

      SET NOCOUNT ON;
      UPDATE [Blogs] SET [Name] = @p0
      WHERE [BlogId] = @p1;
      SELECT @@ROWCOUNT;

      DELETE FROM [Posts]
      WHERE [PostId] = @p2;
      SELECT @@ROWCOUNT;

      DELETE FROM [Posts]
      WHERE [PostId] = @p3;
      SELECT @@ROWCOUNT;

      UPDATE [Posts] SET [BlogId] = @p4, [Title] = @p5
      WHERE [PostId] = @p6;
      SELECT @@ROWCOUNT;

      UPDATE [Posts] SET [BlogId] = @p7, [Title] = @p8
      WHERE [PostId] = @p9;
      SELECT @@ROWCOUNT;

It looks like EF uses Update statements instead of Insert for the new Posts, hence there is 0 row affected.

The solution

I did a lot of research on why this is happening. I did not really find an answer for it. Eventually, I thought that maybe because I provide the new PostId which might make EF think that these posts already exist. I removed the ‘PostId = Guid.NewGuid()’ and it worked.

        [HttpPost]
        public IActionResult Post(BlogDto updatedBlog)
        {
            var blog = _bloggingContext.Blogs
                .Include(x => x.Posts)
                .FirstOrDefault(x => x.BlogId == updatedBlog.Id);

            blog.Name = updatedBlog.Name;
            blog.Posts = updatedBlog.Posts.Select(x => new Post
            {
                Title = x.Title
            }).ToList();

            _bloggingContext.Update(blog);
            _bloggingContext.SaveChanges();

            return NoContent();
        }

This actually makes sense that it would do Insert if the key is not provided. This simplifies the code and only makes one trip to the database. This is the new generated SQL.

      SET NOCOUNT ON;
      UPDATE [Blogs] SET [Name] = @p0
      WHERE [BlogId] = @p1;
      SELECT @@ROWCOUNT;

      DELETE FROM [Posts]
      WHERE [PostId] = @p2;
      SELECT @@ROWCOUNT;

      DELETE FROM [Posts]
      WHERE [PostId] = @p3;
      SELECT @@ROWCOUNT;

      INSERT INTO [Posts] ([PostId], [BlogId], [Title])
      VALUES (@p4, @p5, @p6),
      (@p7, @p8, @p9);

However, I have one issue with this though. It’s OK if you don’t need to use the Post ID after the inserts. If you need to return the new IDs for example, you can’t specify the IDs yourself and you would need to make another trip to the database the grab the new IDs.