SQLite database created in wrong directory in packaged desktop app.

[sjb-sjb]

When using SQLite with EfCore under UWP, new SQLite databases are created in ApplicationData.Current.LocalFolder, in other words [user]\AppData\Local\Packages\[package number]\LocalState.

However, when I compiled my program as a packaged desktop app, the SQLite database was then created in [user]\AppData\Local\VirtualStore\Windows\SysWOW64. I am using WinUI 3 preview 4 with .NET 5 and VS 16.9.0 preview 4, and I created my app with the "Blank App, Packaged (WinUI in Desktop)" template that comes with the WinUI 3 preview 3 extension. I am creating my database using EnsureCreatedAsync() and I am using Microsoft.EntityFrameworkCore.Sqlite v5.0.3.

You may recall that #19754 and #19468 were earlier bugs in which EF did not initialize the SQLite directory. The workaround was to insert the following lines prior to using SQLite:

const int dataDirectoryType = 1;
const int tempDirectoryType = 2;
SQLitePCL.Batteries_V2.Init();
SQLitePCL.raw.sqlite3_win32_set_directory( dataDirectoryType, Windows.Storage.ApplicationData.Current.LocalFolder.Path);
SQLitePCL.raw.sqlite3_win32_set_directory( tempDirectoryType, Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);

When I applied this workaround to my packaged desktop app, the SQLite database was then created in ApplicationData.Current.LocalFolder.

P.S. See also Eric Sink's entry in the SQLite repo

[bricelam]

I suspect this line needs updating for WinUI 3 Desktop apps.

efcore/src/Microsoft.Data.Sqlite.Core/Utilities/ApplicationDataHelper.cs

Line 28 in a872fe1

return Type.GetType("Windows.Storage.ApplicationData, Windows, ContentType=WindowsRuntime")

[sjb-sjb]

Can you suggest when this might make it into a release?

[bricelam]

[sjb-sjb]

Thanks!

[bricelam]

@sjb-sjb I get SQLITE_ERROR from sqlite3_win32_set_directory when I try your workaround.

[bricelam]

@ericsink I think the UWP native assets for e_sqlite3.dll should be used in this case, but I'm not sure NuGet can do that...

[bricelam]

@marb2000 Do you know anyone that can help with NuGet-packaging a library to work with the new stack? The package currently looks like this to get a different native library on UWP, but this falls down in the new world of net5.0-windows TFMs.

sqlitepclraw.lib.e_sqlite3.2.0.6.nupkg
└───runtimes
    ├───win-x64
    │   └───native
    │           e_sqlite3.dll
    │
    └───win10-x64
        └───nativeassets
            └───uap10.0
                    e_sqlite3.dll

[ericsink]

I'm not caught up yet on this current incarnation of this issue. Is the problem that the wrong native DLL is getting deployed?

[sjb-sjb]

@bricelam Have not compiled and run for some time now due to an extensive set of changes in progress, so it is difficult for me to confirm much right now. Broadly I am using WinUI desktop, Net 5, EF Core 5.

[bricelam]

So, I've opened a PR that enables Microsoft.Data.Sqlite to call sqlite3_win32_set_directory in projects targeting net6.0-windows10.0.19041 (or similar)

This still doesn't work when using e_sqlite3.dll since the API returns SQLITE_ERROR. I suspect it's just deploying the wrong (i.e. non-appcontainer) library.

I've verified that the fix works correctly with winsqlite3.dll.

[ericsink]

I suspect it's just deploying the wrong (i.e. non-appcontainer) library.

Maybe. Probably.

But it's also the case that in non-UWP builds of SQLitePCLRaw, sqlite3_win32_set_directory is a stub that returns SQLITE_ERROR.

[bricelam]

Ah, you're right! Using the UWP provider assembly makes it all work. (with my PR changes)

<ItemGroup>
  <PackageReference Include="SQLitePCLRaw.provider.e_sqlite3" Version="2.0.6"  ExcludeAssets="All" GeneratePathProperty="True" />
  <Reference Include="$(PkgSQLitePCLRaw_provider_e_sqlite3)\lib\uap10.0\SQLitePCLRaw.provider.e_sqlite3.dll" />
</ItemGroup>

[ericsink]

@bricelam So... is there some change I need to make? Seems like the handling of things like net6.0-windows10.0.19041 needs to change in my nuget packages?

[bricelam]

Yes, something in the provider package needs to change… I’m just not sure what. Maybe multi-target net5.0-windows in addition to uap10.0? I’m not sure how MSIX-packaging, appcontainers, and NuGet all interact to pick the assets. I haven’t seen any official guidance yet, but it was pretty sparse in UWP too.

[ericsink]

Acknowledged. I'll try to figure this out.

[sjb-sjb]

Under UWP it was not possible to create the SQLite database in any other directory. Is it now possible to create the SQLite database in the user's Documents directory when using a packaged app based on the Windows App SDK, WinUI 3, and .NET 6?