CODEWITHSUNDEEP
c#.netlinqsqlitesystem.data.sqlite
Etienne Charland
Etienne Charland

Reputation: 4024

LINQ to SQLite Cannot Update: "Store update, insert, or delete statement affected an unexpected number of rows"

With System.Data.SQLite and LINQ to Entities, I'm unable to do a simple update.

This code

using (Entities context = new Entities()) {
    var Obj = context.MyTable.Where(m => m.Title.StartsWith("Alaska")).FirstOrDefault();
    Obj.Artist = "A";
    context.SaveChanges();
}

Throws this exception

A first chance exception of type 'System.Data.Entity.Infrastructure.DbUpdateConcurrencyException' occurred in EntityFramework.dll

Additional information: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

How can I fix this?

I'm in an isolated test project so there's nothing else going on around it.

Upvotes: 1

Views: 632

Answers (2)

k3nn
k3nn

Reputation: 171

we might have the same problem when trying to use SaveChanges(). In my case, I used Attach()-SaveChanges() combination to perform update. I am using SQLite DB and its EF provider (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?

Upvotes: 0

Etienne Charland
Etienne Charland

Reputation: 4024

The database was an automated conversion from a SQL Server database, and it turns out UNIQUEIDENTIFIER data type isn't supported in SQLite, yet the table still had the columns and primary keys defined as UNIQUEIDENTIFIER. I got this error whenever I would update a table having a UNIQUEIDENTIFIER key. When updating a table with an INT key, it was alright.

The solution was to change the data type of those columns to TEXT. Then in the .NET mapping classes, the data type changes from Guid to String so I had to adapt the code accordingly but now it works.

Upvotes: 2

Related Questions