Entity Framework Core Sqlite in-memory throws SQLite Error 5: 'unable to delete/modify user-fun-CodePudding

I'm working on a small test service developed with ASP.NET Core 6.0 and Microsoft.EntityFrameworkCore.Sqlite v6.0, which is comprised of a couple of entities and a join table. However, I've started experiencing the following error:

Exception has occurred: CLR/Microsoft.Data.Sqlite.SqliteException
Exception thrown: 'Microsoft.Data.Sqlite.SqliteException' in Microsoft.Data.Sqlite.dll: 'SQLite Error 5: 'unable to delete/modify user-function due to active statements'.'
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteConnection.CreateFunctionCore[TState,TResult](String name, Int32 arity, TState state, Func`3 function, Boolean isDeterministic)
   at Microsoft.Data.Sqlite.SqliteConnection.CreateFunction[T1,T2,TResult](String name, Func`3 function, Boolean isDeterministic)
   at Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteRelationalConnection.InitializeDbConnection(DbConnection connection)
   at Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteRelationalConnection..ctor(RelationalConnectionDependencies dependencies, IRawSqlCommandBuilder rawSqlCommandBuilder, IDiagnosticsLogger`1 logger)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_ContextServices()
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.Set[TEntity]()
   at TestProject.AvailabilityService.Persistence.AvailabilityDbContext.get_GroupsOfPeople() in /Users/ram/Development/TestProject/src/Persistence/AvailabilityDbContext.cs:line 18

Googling for this particular error message came up empty. Does anyone have any insight on what might be causing this problem?

CodePudding user response：

The error you are running into is caused by trying to access Sqlite from multiple threads. Concurrency is supported up to a point (when read-only operations are performed) but once you start inserting or updating records things get tricky. The error (5) corresponds to documentation on Sqlite's result and error codes page:

https://www.sqlite.org/rescode.html#busy

The SQLITE_BUSY result code indicates that the database file could not be written (or in some cases read) because of concurrent activity by some other database connection, usually a database connection in a separate process.

CodePudding user response：

EF Core source code shows that you have used the same DbConnection while defining DbContextOptions. It may cause problems with concurency if the same connection is used concurrently.

Use connection string instead.

Link to source code