0

From what I have found on the web, Sqlite has a Json extension which support the Json operators -> and ->> but that it's not included by default. Is there a way to enable this extension when using Entity Framework Core?

My situation is, I'm using Entity Framework Core with a PostgreSQL database in production but I'm using an Sqlite in memory database for unit tests.

I am executing some SQL in a repository that I'm having trouble unit testing. It uses PostgreSQLs Json operator ->> but when the unit tests try to run it with Sqlite, it doesn't recognise the operator.

The line of code in question is

dbContext.Database.ExecuteSqlInterpolatedAsync($"DELETE FROM clients.client_history WHERE new_value ->> 'id' = {clientId.ToString()}");

I understand that Sqlite is not going to be a perfect match to PostgreSQL in all situations but it would be helpful if it could match the behaviour a bit more in this scenario. Using PostgreSQL for the unit tests is not a practical solution for us due to performance / overhead in setting up the database. Currently, this is the only part of my repository where Sqlite doesn't match the behaviour of PostgreSQL closely enough to be useful.

I'm using .Net6 with Entity Framework Core 6. I'm developing on Windows and deploying to Linux based Docker / Kubernetes (depending on environment). We currently plan on upgrading to .Net7 / EF Core 7 at some point in the future (once it has been RTW, obviuosly) so if EF Core 7 introduces a solution to this, that would be useful to know, even if EF Core 6 can't do it.

Mog0
  • 1,689
  • 1
  • 16
  • 40

1 Answers1

0

SQLite supports this operator since 3.38. You need to update your SQLite module used by EFC.

PChemGuy
  • 1,582
  • 3
  • 6
  • 18
  • I'm already using the latest version of the Microsoft.EntityFrameworkCore.Sqlite nuget package (6.0.9). Not sure if there's any way to update the SQLite module separately - that's kind of what the question's about. – Mog0 Oct 06 '22 at 12:03
  • It does not matter what the version of the framework is. Execute `SELECT sqlite_version();` via the framework to see the SQLite version. I work on Windows myself. More often than not, it is possible to swap the SQLite dll. But you need to know the location of the module used. I did it to Python, SQLiteStudio, and DB Browser for SQLite. Never worked with EFC. – PChemGuy Oct 06 '22 at 12:06
  • It seems that the version included in the package is 3.35.5 and thus too early to have the operators included by default. It's not as simple as swapping the dll as the package only includes a single dll called `Microsoft.EntityFrameworkCore.Sqlite.dll` – Mog0 Oct 06 '22 at 15:43
  • I do not have it installed, so I cannot investigate further. But most likely SQLite is in a separate module, which may have prefixes/suffixes. The question is which is it. – PChemGuy Oct 06 '22 at 16:09
  • 1
    To update to a newer version of SQLite, install the latest version of this package: `dotnet add package SQLitePCLRaw.bundle_e_sqlcipher --version 2.1.2` – bricelam Oct 13 '22 at 17:20