Revisit – EF Core error when updating nested list of an object

/ Leave a Comment

In this previous post, I talked about how EF uses Update statements instead of Insert when adding new items with the item key populated to a list property of an object and then SaveChanges. In that post, the solution to get it working was to not populate the key property when adding the item to the list. EF will treat that as an Insert.

However, that seems like a hack and I thought it was strange that EF could not figure it out itself. Also, there are use cases where you might not be able to avoid not setting the key property.

I come across this very use case recently where our API receives a request from a client to add an item with the key provided in the payload. I cannot just clear the key and let EF generates one.

After some doing some research, I found that I can tell EF to not generate the key. If I pass in an existing key, EF is able to track changes and performs an Update. If an item is added with a new key, EF will do an Insert.

In the DB context, override method OnModelCreating and add the following

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder
                .Entity<Blog>()
                .Property(x => x.BlogId)
                .ValueGeneratedNever();

            modelBuilder
                .Entity<Post>()
                .Property(x => x.PostId)
                .ValueGeneratedNever();
        }

Now when I run the same code from the last post, there’s no exception and the database is updated correctly. the generated SQL also looks correct

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

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

UPDATE [Posts] SET [Title] = @p3
WHERE [PostId] = @p4;
SELECT @@ROWCOUNT;

INSERT INTO [Posts] ([PostId], [BlogId], [PublishedDate], [Title])
VALUES (@p5, @p6, @p7, @p8);
',N'@p1 uniqueidentifier,@p0 nvarchar(4000),@p2 uniqueidentifier,@p4 uniqueidentifier,@p3 nvarchar(4000),@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 datetime2(7),@p8 nvarchar(4000)',@p1='33A96D53-39B9-4CC4-BE08-08D8C0192465',@p0=N'JustSimplyCode-b7fb2145-f3a0-4707-a084-cd0e26e623e3-b3ccbc33-2fcb-4327-855d-0053fb271abe-15e2aa34-ccb4-45b6-8364-2992ce66822c-51543fa7-d731-4c30-8e17-59868b5c87e3-e8fc7830-14bd-42e8-a472-618aa50aa2f9',@p2='ABBABDBF-1451-4B7C-B6AB-BFA540001949',@p4='154510C8-63BB-4591-10D8-08D8C02074C2',@p3=N'AAA-Updated',@p5='F49A0161-5F2B-40BE-B034-A37A6E9AE639',@p6='33A96D53-39B9-4CC4-BE08-08D8C0192465',@p7='0001-01-01 00:00:00',@p8=N'ZZZ'

Leave a Reply