Unable to load bundle_e_sqlcipher with EF Core 3.0

[danielkraut]

This issue originated as an issue in the EntityFramework.Core project (1790) but was closed there and was requested to be opened here to track the work, as it probably is more a SQLitePCL.raw issue.

The problem: The EF.Core/Microsoft.Data.SQlite package in version 3.0 updated the dependecy on SQLitePCL.raw to target version >= 2.0. When using a sqlitecipher bundle, this requires to use the new NuGet package bundle_e_sqlcipher. But I was unable to start initialize SQLCipher in my work project (targeting .NET framework 4.7.2) even after using the new package. Quoted from the original issue:

From what from what I was able to deduce I think that some weird combination of NuGet packages might be the issue on my part, if you say that EFCore 3.0 works with SQLCipher for you. I tried stepping through the Batteries_V2.Init method (with JetBrains Rider) and method private static IntPtr MyLoad(...) searches for C:\Users.....\Project.Wpf\bin\x86\Debug\runtimes\win-x86\native\e_sqlite3.dll which is not there (so it then throws "Not found" exception). The only DLL in that folder is "e_sqlcipher.dll". If I add either package Microsoft.Data.SQLite or bundle_green to the Wpf project, the file e_sqlite3.dll is there but the encryption disabled, which I guess is correct behaviour when the sqlcipher DLL is not actually used. But why Batteries{_V2}.Init() does not target the SQLCipher provider I am not able to figure out. I tried to add the provider packages explicitly but without a positive results I also did clean solution and remove packages folder many times to be sure that the changes have propagated.

The new .NET Core 3.0 came with WPF support and we are thinking about updating (well I'd say 3.1 is more plausible). If the time comes I will try to investigate the issue again.

[ericsink]

OK I'm confused.

First of all, the title of this issue speaks of e_sqlite3 but the problem speaks of sqlcipher. I assume the title is simply mistaken?

The original issue mentions the current lack of a workable solution for Xamarin.Mac. I kinda thought that was what this issue was going to be. I do still hope to find a solution for that.

The original issue indicates you had a problem getting bundle_e_sqlcipher to work with net472. With nothing else in play, that could be worked out easily. But does EF Core 3.0 even support .NET Framework?

[danielkraut]

I am sorry if I didn't described my issue badly I will try again, hopefully with better clarity.

We implement some in-house libraries which depend on Microsoft.Data.Sqlite, which itself depends on SQLitePCL.raw. We include those libraries in our app which we then build for Windows (net472), Xamarin.iOS and Xamarin.Mac.

As described in your wiki, if we include library which depends on SQLitePCL.raw, we should use one of the bundle packages. During the project development we moved from bundle_green to the bundle_sqlcipher package to enable encryption. We used Microsoft.Data.Sqlite in version 2.0.0, which itself depends on SQLitePCLRaw.bundle_green version =< 1.1.7. In the csproj files (all platforms) this is the configuration we used (I didn't write this):

<PackageReference Include="SQLitePCLRaw.bundle_green" Version="1.1.7" ExcludeAssets="All" />
<PackageReference Include="SQLitePCLRaw.bundle_sqlcipher" Version="1.1.11" />

My job was to update the Microsoft.Data.Sqlite to the newest version (which is now 3.0 == EF.Core 3.0) which depends on bundle_e_sqlite >= 2.0.0. My original problem was, that no matter what I did, I could not get the right combination of bundle_* packages to get the encryption from e_sqlcipher working. I was able to use it without encryption, but including the bundle_e_sqlcipher either broke build (type Batteries exists in both libraries) or broke runtime with exception on Batteries.Init (mainly on Xamarin.iOS if I remember correctly). That is why I originaly opened the issue. My second problem was that Xamarin.Mac is not supported in SQLitePCL 2.0, but I only found out about that later after trying different solutions and that killed my effort entirely because it is one of the required platforms we need to support.

You are right, the title is misleading, I will change the bundle name in it. Please feel free to also change the issue title to be more appropriate. I am not an English native speaker.

---

My solution for now is that I updated the Microsoft.Data.Sqlite to 2.2.6, which depends on bundle_green >= 12.0.0. From trial and error I concluded, that to get it working with sqlcipher on all 3 required platforms, I need to include bundle_green with ExcludeAssets="All" and include bundle_sqlcipher in version greater than 12.0.0 (again issues were mainly on Xamarin.iOS). I don't know enough about NuGet/dependencies to explain, why this is the working setup. We also use the Microsoft.AppCenter package, which I saw in some other GitHub issue that may cause some probles and you recommended to include bundle_green with ExcludeAssets (speaking from memory, I don't have a link to that thread). This is my current .csproj for anyone also having these issues:

<PackageReference Include="SQLitePCLRaw.bundle_green" Version="1.1.12" ExcludeAssets="All" />
<PackageReference Include="SQLitePCLRaw.bundle_sqlcipher" Version="1.1.14" />

[ericsink]

Very good. Thanks for all the details.

Yes, AppCenter is a bit of a troublemaker, because it brings in bundle_green as a dependency. The PackageReference with ExcludeAssets is basically just overriding that and removing bundle_green.

Sorry you've had so much trouble. If someone wanted to experience as many problems as possible, your configuration is a nearly perfect way to do that. :-)

I still need a workaround for the difficulties on Xam.Mac, but even then, AppCenter will probably prevent you from updating. Perhaps we can get the AppCenter folks to update their dependencies.

[danielkraut]

Both libraries - Microsoft.Data.Sqlite and Microsoft.AppCenter have dependency on bundle_green (AppCenter through dependency on sqlite-net-pcl) so both of them are probably causing problems for us (Sqlite is still my main suspect because I had to raise the bundle_green version to 1.1.12 after updating it). We use AppCenter solely on Xam.iOS though.

Thank you for trying to help me, I appreciate it. :-)

[ericsink]

I believe that Microsoft.Data.Sqlite:

1. Now takes it dependency on bundle_e_sqlite3 instead of bundle_green.
2. Is available in an alternate package which depends on SQLitePCLRaw.Core instead of a bundle, thus avoiding these problems.

But the real issue here is that SQLitePCLRaw 2.x is a compatibility break with 1.x. In order to switch to Microsoft.Data.Sqlite 3.0, everything in your app that uses SQLitePCLRaw (directly or indirectly) must be compatible with SQLitePCLRaw 2.x.

What I did not realize (or had forgotten) is that AppCenter gets its dependency through sqlite-net-pcl, which offers hope. The maintainer of that package has a beta which is compatible with SQLitePCLRaw 2.x.

[danielkraut]

Hi ericsink, I am writing mainly to say that I was browsing through issues here and saw #358 and I if you would implement the workaround for Xamarin.Mac it would be much appretiated :-) .

Also I was able to get around the rest of my issues (other than Xamarin Mac plaform) by using Microsoft.Data.Sqlite .Core package instead of Microsoft.Data.Sqlite. This was recommended somewhere on stacoverflow or elsewhere I don't remember. After adding

<!-- Microsoft.Data.Sqlite v3.1.x uses this exact version -->
<PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.0.2" ExcludeAssets="All" /> 
<!-- Add higher version to enable sqlite database encryption -->
<PackageReference Include="SQLitePCLRaw.bundle_e_sqlcipher" Version="2.0.3">

I was able to have encrypted database on Windows and Xamarin.iOS (after setting correct encryption parameters or updating database to newest sqlite3). Which is exactly what you have recommended with:

I believe that Microsoft.Data.Sqlite: ... 2. Is available in an alternate package which depends on SQLitePCLRaw.Core instead of a bundle, thus avoiding these problems.

Sorry that I didn't realise that before.

Also want to note that I may have mistified you saying that AppCenter uses sqlite-net-pcl, which was true in version 2.1.1 I still use, but since version 3.0.0 depend directly on bundle_green.

[ericsink]

Closing old/stale issue.