Microsoft.EntityFrameworkCore - 'database operation expected to affect 1 row(s) but actually affected 0 row(s)' error

I've recently been struggling with a problem with one of my projects. I had a parent entity that could have several children related entities. Typically, if I'm building a database, I always use a long property type. However, I'm working with an existing database and its primary key is a string. This is what introduced the problem for me. When setting up the entity you need to make sure you don't set the DatabaseGeneratedOption to be Identity. You should make sure to set it to None.

I thought I would share an example to hopefully help you, the person reading this if you ever come across the situation. If you search around the internet most people have solutions but it’s in the way you setup the keys. I couldn't find anyone that referenced this scenario, so here it is.

Here was my entity originally:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Column(Order = 1)]
public string MyChildEntitySeqNum { get; set; }

[Key]
public string MyEntityId { get; set; }

When I ran my code, it got to the part where it tries to save the entity. But returns the following error:

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..'

After spending some time banging my head against a wall, I noticed that the SQL entity framework was generating was wrong. It was trying to make an Update instead of an Insert for the child entity.

So that led me to go take a look at my entity. Which I found the DatabaseGeneratedOption was set. Bingo! I need to change that to None. Which I did:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Column(Order = 1)]
public string MyChildEntitySeqNum { get; set; }

[Key]
public string MyEntityId { get; set; }

After making that change and letting it run, entity framework handled it as an Insert which was expected. Everything worked!

So, the solution here, whenever you're working with a string as a primary key, never set it as the identity column. This will cause you some pain and a loss of many hours. I've thrown together a solution for you to play with and try yourself.