RemoveRange Error when removing multiple records in UWP and SQLite

[waterfallbay]

I have run the below code to get all shop types from SQLite with shopTypeId >2, and then removed them all in a batch:

EwDataContext db = new EwDataContext();
var shopTypeListToBeDeleted = db.ShopType.Where(x => x.ShopTypeid > 2);
db.ShopType.RemoveRange(shopTypeListToBeDeleted);
db.SaveChanges();

However, the program return me an error by stating that:

"SQLite Error 1: 'SQL logic error or missing database'"

   at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.Data.Entity.Update.ReaderModificationCommandBatch.Execute(IRelationalTransaction transaction, IRelationalTypeMapper typeMapper, DbContext context, ILogger logger)

I have no such error if the size of the list to be deleted is 1, or I delete that one by one instead of deleting them all at a batch.

Environment:
VS 2015 Enterprise
EntityFramework.Commands: "7.0.0-beta6"
EntityFramework.SQLite: "7.0.0-beta6"
Microsoft.CodeAnalysis.CSharp: "1.0.0"

Please help. Thanks.

[natemcmaster]

To diagnose this better, we need to log what is actually being sent to SQLite. It's unfortunately hacky, but here are some instructions for adding a logger in beta6.

var services = new ServiceCollection()
                .AddEntityFramework()
                .AddSqlite()
                .ServiceCollection()
                .AddInstance<ILoggerFactory>(new TestSqlLoggerFactory())
                .BuildServiceProvider();

var context = new EwDataContext(services); // will need to add new ctor that takes IServiceProvider
// proceed with business as usual

(this is what we do in NorthwindQuerySqliteFixture). Use the TestSqlLoggerFactory from this file.

When you hit the exception, break execution and examine what is in TestSqlLoggerFactory.Sql.

[waterfallbay]

We are having problem in using TestSqlLoggerFactory in our UWP project. We can't use System.Runtime.Remoting.Messaging for the CallContext class. Is there any work around?

[natemcmaster]

@waterfallbay You could just try using the DNXCORE implementation instead.
System.Runtime.Remoting is in mscorlib for full .NET. Which version of .NET are you targetting?

[natemcmaster]

(Doh - I read more carefully. You're on UWP.) UWP doesn't have the DNXCORE50 compile symbol defined. Change this to NETFX_CORE.

[waterfallbay]

@natemcmaster TestSqlLoggerFactory works now after changing the symbol to NETFX_CORE. Thanks. But the logger still does not work, it show errors on

var services = new ServiceCollection()
                .AddEntityFramework()
                .AddSqlite()
                .ServiceCollection()
                .AddInstance<ILoggerFactory>(new TestSqlLoggerFactory())
                .BuildServiceProvider();

stating that it does not contain a definition for "ServiceCollection", please help.

[natemcmaster]

@waterfallbay Ah yes. We added .ServiceCollection() as a helper to our testing class. This has the exact same effect.

var collection = new ServiceCollection();
collection.AddEntityFramework().AddSqlite();
collection.AddInstance<ILoggerFactory>(new TestSqlLoggerFactory());
var services = collection.BuildServiceProvider();

[waterfallbay]

@natemcmaster i have implemented what you have suggested and run successfully. However, would you please advise where would TestSqlLoggerFactory.Sql file saved? Since i can't locate it.
Thanks.

[natemcmaster]

When you hit the exception, break execution and examine what is in TestSqlLoggerFactory.Sql.

To clarify, it does not save a file. The sql can be inspected in memory. Run your app in debug mode. When you hit your error, inspect the static field TestSqlLogger.Sql in your IDE.

[waterfallbay]

@natemcmaster I've make a breakpoint in TestSqlLoggerFactory.cs > SqlLogger > _testOutputHelper?.WriteLine(format + Environment.NewLine);
And after checking the log, the following is what the program generated during execution:

"Compiling query model: 'from ShopType x in value(Microsoft.Data.Entity.Query.EntityQueryable`1[POS.WebService.DataContext.ShopType]) where ([x].ShopTypeid > 2) select [x]'"

"Optimized query model: 'from ShopType x in value(Microsoft.Data.Entity.Query.EntityQueryable`1[POS.WebService.DataContext.ShopType]) where ([x].ShopTypeid > 2) select [x]'"

"Tracking query sources: [x]"

"Compiled query expression."

"Opening connection 'Data source=C:\\Users\\billychan\\AppData\\Local\\Packages\\6dfbae8d-5a3d-4ef9-8f6c-e1a1dece4abb_qp30qty5v1528\\LocalState\\Data\\ewPos4.db'."

"SELECT \"x\".\"ShopTypeid\", \"x\".\"AccountId\", \"x\".\"AltTypeDesc\", \"x\".\"AltTypeName\", \"x\".\"CreatedBy\", \"x\".\"CreatedDate\", \"x\".\"Enabled\", \"x\".\"ModifiedBy\", \"x\".\"ModifiedDate\", \"x\".\"TypeDesc\", \"x\".\"TypeName\"\r\nFROM \"ShopType\" AS \"x\"\r\nWHERE \"x\".\"ShopTypeid\" > 2"

"Closing connection 'Data source=C:\\Users\\billychan\\AppData\\Local\\Packages\\6dfbae8d-5a3d-4ef9-8f6c-e1a1dece4abb_qp30qty5v1528\\LocalState\\Data\\ewPos4.db'."

"Opening connection 'Data source=C:\\Users\\billychan\\AppData\\Local\\Packages\\6dfbae8d-5a3d-4ef9-8f6c-e1a1dece4abb_qp30qty5v1528\\LocalState\\Data\\ewPos4.db'."

"Beginning transaction with isolation level 'Unspecified'."

"@p0: 3\r\n@p1: 9216"

"DELETE FROM \"ShopType\"\r\nWHERE \"ShopTypeid\" = @p0 AND \"AccountId\" = @p1;\r\nSELECT changes();"

"@p2: 4\r\n@p3: 9216"

"DELETE FROM \"ShopType\"\r\nWHERE \"ShopTypeid\" = @p2 AND \"AccountId\" = @p3;\r\nSELECT changes();"

"Closing connection 'Data source=C:\\Users\\billychan\\AppData\\Local\\Packages\\6dfbae8d-5a3d-4ef9-8f6c-e1a1dece4abb_qp30qty5v1528\\LocalState\\Data\\ewPos4.db'."

"An exception occurred in the database while saving changes.\r\nMicrosoft.Data.Entity.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'SQL logic error or missing database'\r\n   at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db)\r\n   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)\r\n   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at Microsoft.Data.Entity.Update.ReaderModificationCommandBatch.Execute(IRelationalTransaction transaction, IRelationalTypeMapper typeMapper, DbContext context, ILogger logger)\r\n   --- End of inner exception stack trace ---\r\n   at Microsoft.Data.Entity.Update.ReaderModificationCommandBatch.Execute(IRelationalTransaction transaction, IRelationalTypeMapper typeMapper, DbContext context, ILogger logger)\r\n   at Microsoft.Data.Entity.Update.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)\r\n   at Microsoft.Data.Entity.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)\r\n   at Microsoft.Data.Entity.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)\r\n   at Microsoft.Data.Entity.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)\r\n   at Microsoft.Data.Entity.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)"

Another observation is, my test function should delete 3 rows from DB ( we have 5 rows and select all those ID > 2 to delete) but the program only generate 2 delete statements to delete id=3 and id=4 only and throw the above exception, id=5 is leaving intact.

Please help.

[natemcmaster]

It appears the database isn't missing, so this is likely the same issue as aspnet/Microsoft.Data.Sqlite#116. This issue is caused by the goofy filesystem permissions on UWP. Can you try the workaround in this post? (i.e. add the following to your code at the beginning of your application, before EF is every opened).

// add this to your startup class
[DllImport("sqlite3", EntryPoint = "sqlite3_win32_set_directory", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]
static extern int SetDirectory(uint directoryType, string directoryPath);

//before EF runs, call...
SetDirectory(2, Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);

[waterfallbay]

@natemcmaster i can solve the issue with your workaround so your guess is correct and this should be the same issue as in aspnet/Microsoft.Data.Sqlite#116