System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Ticket Hash: 55e50721c86cbb861b9790202bda35970cf82efb
Title: SQLite EF6 with GUID primary key causes DbUpdateConcurrencyException
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Works_As_Designed
Last Modified: 2015-11-29 01:52:40
Version Found In: Build 1.0.98.0
User Comments:
anonymous added on 2015-11-23 12:03:41: (text/x-fossil-plain)
DbUpdateConcurrencyException is thrown when I try to update a row of a table which has a guid primary key. 
Exception: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.


I'm using EF6 with SQLite dataprovider version 1.0.98.0.
I have created a sample project and I can send across the sample project too if needed.

anonymous (claiming to be rengarajiyer) added on 2015-11-23 12:48:07: (text/x-fossil-plain)
I am not able to upload my sample project, and if I am provided me with an email id, I can send you the sample.

mistachkin added on 2015-11-24 14:30:42: (text/x-fossil-plain)
Are you using the latest version of the EntityFramework package (i.e. 6.1.3)?

mistachkin added on 2015-11-24 14:39:23: (text/x-fossil-plain)
From the example, I'm getting the exception:

Unable to determine the provider name for provider factory of type 'System.Data.SQLite.SQLiteFactory'. Make sure that the ADO.NET provider is installed or registered in the application config.
   at System.Data.Entity.Utilities.DbProviderFactoryExtensions.GetProviderInvariantName(DbProviderFactory factory)
   at System.Data.Entity.Infrastructure.DependencyResolution.DefaultInvariantNameResolver.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.CachingDependencyResolver.<>c__DisplayClass1.<GetService>b__0(Tuple`2 k)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at System.Data.Entity.Infrastructure.DependencyResolution.CachingDependencyResolver.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<>c__DisplayClass3.<GetService>b__0(IDbDependencyResolver r)
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.RootDependencyResolver.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<>c__DisplayClass3.<GetService>b__0(IDbDependencyResolver r)
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.CompositeResolver`2.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetService[T](IDbDependencyResolver resolver, Object key)
   at System.Data.Entity.Utilities.DbConnectionExtensions.GetProviderInvariantName(DbConnection connection)
   at System.Data.Entity.Core.EntityClient.EntityConnection.get_ConnectionString()
   at System.Data.Entity.Core.Objects.ObjectContext..ctor(EntityConnection connection, Boolean isConnectionConstructor, ObjectQueryExecutionPlanFactory objectQueryExecutionPlanFactory, Translator translator, ColumnMapFactory columnMapFactory)
   at System.Data.Entity.Core.Objects.ObjectContext..ctor(EntityConnection connection)
   at System.Data.Entity.Internal.MockingProxies.ObjectContextProxy.CreateNew(EntityConnectionProxy entityConnection)
   at System.Data.Entity.Internal.ClonedObjectContext..ctor(ObjectContextProxy objectContext, String connectionString, Boolean transferLoadedAssemblies)
   at System.Data.Entity.Internal.InternalContext.UseTempObjectContext()
   at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
   at System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c)
   at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input)
   at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action)
   at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
   at System.Data.Entity.Internal.InternalContext.Initialize()
   at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
   at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
   at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
   at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
   at System.Linq.Queryable.First[TSource](IQueryable`1 source)
   at SampleEF6ToSQLite.Program.Main() in SampleEF6ToSQLite\Program.cs:line 16

mistachkin added on 2015-11-24 14:54:37: (text/x-fossil-plain)
After adding the missing provider to the configuration file, I get the following
output:

Connecting to database and retrieving GUID values!
Before Changes . UserName is : NewValue
Changes Saved. UserName is : NewValue_1
Press any key to continue.

mistachkin added on 2015-11-24 15:04:34: (text/x-fossil-plain)
Running the application a second time produces the output:

Connecting to database and retrieving GUID values!
Before Changes . UserName is : NewValue_1
Changes Saved. UserName is : NewValue_1_1
Press any key to continue.

anonymous added on 2015-11-26 07:43:52: (text/x-fossil-plain)
My EF version was 6.0. 

I verified with EF 6.1.3 and the issue is no more present. 

The issue was that, from NuGET when I install System.Data.SQLite package, it is installing EF6.0 automatically instead of the latest version EF6.1

Now I install EF6.1 first manually and then install System.Data.SQLite package and the issue isnt present.

Thanks for your effort.

anonymous added on 2015-11-26 13:25:24: (text/x-fossil-plain)
Hi Joe,

Sorry that I had sent to you the wrong sample. I infact used string as primary key in the sent one.. instead of GUID primary key..

I have sent you an updated sample with GUID primary key. Please verify it.

mistachkin added on 2015-11-29 01:44:13: (text/x-fossil-plain)
I'm able to reproduce the issue now.  Adding the following statement into the
example enables additional diagnostic information in the debugger output:

Environment.SetEnvironmentVariable(
    "DefaultFlags_SQLiteConnection",
    "LogPrepare, LogPreBind, LogBind"
);

mistachkin added on 2015-11-29 01:50:05: (text/x-fossil-plain)
Using the ".dump" command in the interactive shell produces the following output:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "UserDetails" ("UserId" guid PRIMARY KEY  NOT NULL  DEFAULT (null) ,"UserName" nvarchar(256) NOT NULL );
INSERT INTO "UserDetails" VALUES('6bdb33fe-cb7f-41ef-a787-2e437c4c1940','NewValue_1');
INSERT INTO "UserDetails" VALUES('6bdb33fe-cb7f-41ef-a787-2e437c4c1941','User2');
COMMIT;

mistachkin added on 2015-11-29 01:52:40: (text/x-fossil-plain)
When using "BinaryGUID=True" in the connection string, the data stored in the
database column must be a blob with a length of 16 bytes, not a formatted GUID
string.  In your example, changing it to "BinaryGUID=False" resolves the issue.