EF 6 SaveChanges Store update, insert, or delete statement affected an unexpected number of rows (0)

Question

We have two databases. This exception gets thrown only when the connection string points to the second one and I'm doing an edit. It shouldn't be an issue with the code since it works as expected with the first database.

I've tried:

try 
{
    context.SaveChanges();
} 
catch (OptimisticConcurrencyException) 
{
    context.Refresh(RefreshMode.ClientWins, db.Articles);
    context.SaveChanges();
}

Updating the entity's properties both by both mapping them one at a time and using EntityState.Modified
https://msdn.microsoft.com/en-us/library/jj592904(v=vs.113).aspx
Verifying all primary keys are in order

This is a good read for solving concurrency issues in EF6: https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application — Bradley Uffner, Jan 26 '18 at 13:54
You said it yourself : it should be an issue with the database. You may compare these 2 db and see on what they differ (using SQL-Server Data Tools, for instance, is you use SQL-Server). — Dude Pascalou, Jan 26 '18 at 17:14

score 0 · Answer 1 · answered Sep 09 '20 at 03:57

I might have the same problem as you as I am using SQLite DB and its EF provider (and I am also using SQLServer DB and EF) when trying to update record using Attach() and then SaveChanges() combination (and yes, the same code works in SQLServer DB without problem).

I found out, when your DB column has GUID (or UniqueIdentity) in SQLite and your model is nvarchar, SQLIte EF treats it as Binary(i.e., byte[]) by default. So when SQLite EF provider tries to convert GUID into the model (string in my case) it will fail as it will convert to byte[]. The fix is to tell the SQLite EF to treat GUID as TEXT (and therefore conversion is into strings, not byte[]) by defining "BinaryGUID=false;" in the connectionstring (or metadata, if you're using database first) like so:

  <connectionStrings>
    <add name="Entities" connectionString="metadata=res://savetyping...=System.Data.SQLite.EF6;provider connection string=&quot;data source=C:\...\db.sqlite3;Version=3;BinaryGUID=false;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

Link to the solution that worked for me: How does the SQLite Entity Framework 6 provider handle Guids?

EF 6 SaveChanges Store update, insert, or delete statement affected an unexpected number of rows (0)

1 Answers1