Save SQLite file to MyDocuments

[sjb-sjb]

With the resolution of #24213, when building a packaged app with WindowsAppSDK we can now save SQLite databases into the AppDir:

        string folder = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
        optionsBuilder.UseSqlite($"Data Source={Path.Join(folder,"blogging.db")}");

However, if we substitute SpecialFolder.MyDocuments, an error occurs. To my (limited) knowledge there is not a hard reason for this because the file redirection model would seem to say that outside of the AppDir, as long as the user has permissions on the directory they can access files there.

There would be many advantages to being able to save an SQLite database to My Documents. One key reason to do so is so that SQLite can then be used as a document format, with the documents remaining in place in MyDocuments if the app is removed. I believe there is even a MS guideline somewhere saying roughly that AppDir should only be used for non-critical data that can be deleted without significant repercussions. In addition, MyDocuments is backed up to cloud storage by OneDrive sync, providing a high level of security and retention of the data.

If app developers cannot save SQLite databases to MyDocuments but need to keep non-transient data in SQLite, then they are forced to write their own code to copy the SQLite file over to MyDocuments. That is not very easy to do well, and is essentially duplicating existing Windows sync features that apply to non-AppDir directories.

Would welcome this feature and/or any suggestions on workarounds.

[ajcvickers]

an error occurs.

What error?

[sjb-sjb]

Well the error in my app was "Access denied" but this seems to have been due to a problem in my code.
When I tried to reproduce this in a small example, I got different errors -- and the same errors for both directories.

1. When doing bloggingContext.Database.EnsureCreated() and then bloggingContext.Dispose(), I got the following:

onecoreuap\base\appmodel\statemanager\winrt\lib\windows.storage.applicationdatafactory.server.cpp(235)\Windows.Storage.ApplicationData.dll!1AB4E1E9: (caller: 0FA07089) ReturnHr(1) tid(2790) 8000000B The operation attempted to access data outside the valid range
    Msg:[User S-1-5-21-10732632-1214145803-3743589654-1003] 
onecoreuap\internal\shell\inc\private\StateRepoUtil.h(40)\Windows.FileExplorer.Common.dll!7537B889: (caller: 7537ED48) ReturnHr(1) tid(29ec) 8000000B The operation attempted to access data outside the valid range
onecoreuap\internal\shell\inc\private\StateRepoUtil.h(40)\Windows.FileExplorer.Common.dll!7537B889: (caller: 7537F112) ReturnHr(2) tid(29ec) 8000000B The operation attempted to access data outside the valid range
onecoreuap\internal\shell\inc\private\StateRepoUtil.h(40)\Windows.FileExplorer.Common.dll!7537B889: (caller: 7537F2C7) ReturnHr(3) tid(29ec) 8000000B The operation attempted to access data outside the valid range
onecoreuap\internal\shell\inc\private\StateRepoUtil.h(40)\Windows.FileExplorer.Common.dll!7537B889: (caller: 7537EF21) ReturnHr(4) tid(29ec) 8000000B The operation attempted to access data outside the valid range

2. Then when I exited the app I got this:

Exception thrown at 0x7642B922 (KernelBase.dll) in Test.exe: WinRT originate error - 0x80000013 : 'The given object has already been closed / disposed and may no longer be used.'.
onecore\com\combase\winrt\error\restrictederror.cpp(1016)\combase.dll!75C9B897: (caller: 75BACA86) ReturnHr(1) tid(2790) 8007007E The specified module could not be found.
Exception thrown at 0x7642B922 (KernelBase.dll) in Test.exe: WinRT originate error - 0x80000013 : 'The given object has already been closed / disposed and may no longer be used.'.
onecore\com\combase\winrt\error\restrictederror.cpp(1016)\combase.dll!75C9B897: (caller: 75BACA86) ReturnHr(2) tid(2790) 8007007E The specified module could not be found.
Exception thrown at 0x7642B922 (KernelBase.dll) in Test.exe: WinRT originate error - 0x80000013 : 'The given object has already been closed / disposed and may no longer be used.'.
onecore\com\combase\winrt\error\restrictederror.cpp(1016)\combase.dll!75C9B897: (caller: 75BACA86) ReturnHr(3) tid(2790) 8007007E The specified module could not be found.

3. Then I was left with three files in MyDocuments directory: blogging.db, blogging.db-shm, and bloggin.db-wal, instead of one.

4. HOWEVER, what I found out is that when I re-ran the test but using SpecialFolder.LocalApplicationDirectory, I got exactly the same thing. Only the three files were left in the package directory LocalCache.

5. Here is the configuration, and below that is the code. I used a new packaged Desktop WinUI 3 app.

Microsoft Visual Studio Community 2022
Version 17.1.0
VisualStudio.17.Release/17.1.0+32210.238
Microsoft .NET Framework
Version 4.8.04084

Installed Version: Community

Visual C++ 2022   00482-90000-00000-AA995
Microsoft Visual C++ 2022

ASP.NET and Web Tools 2019   17.1.358.51495
ASP.NET and Web Tools 2019

Azure App Service Tools v3.0.0   17.1.358.51495
Azure App Service Tools v3.0.0

C# Tools   4.1.0-5.22109.6+0c82c4114a4e4b8b723b915eee3b13261db6717f
C# components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.

Common Azure Tools   1.10
Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.

Microsoft JVM Debugger   1.0
Provides support for connecting the Visual Studio debugger to JDWP compatible Java Virtual Machines

Microsoft MI-Based Debugger   1.0
Provides support for connecting Visual Studio to MI compatible debuggers

Microsoft Visual C++ Wizards   1.0
Microsoft Visual C++ Wizards

Microsoft Visual Studio VC Package   1.0
Microsoft Visual Studio VC Package

NuGet Package Manager   6.1.0
NuGet Package Manager in Visual Studio. For more information about NuGet, visit https://docs.nuget.org/

TypeScript Tools   17.0.1229.2001
TypeScript Tools for Microsoft Visual Studio

Visual Basic Tools   4.1.0-5.22109.6+0c82c4114a4e4b8b723b915eee3b13261db6717f
Visual Basic components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.

Visual F# Tools   17.1.0-beta.21610.4+07b5673e4f2fa7630e78abe37f16b372353a7242
Microsoft Visual F# Tools

Visual Studio Code Debug Adapter Host Package   1.0
Interop layer for hosting Visual Studio Code debug adapters in Visual Studio

Visual Studio IntelliCode   2.2
AI-assisted development for Visual Studio.

namespace Test
{
    public sealed partial class MainWindow : Window
    {
        public MainWindow()
        {
            this.InitializeComponent();
        }

        private void MyButton_Click(object sender, RoutedEventArgs e)
        {
            myButton.Content = "Clicking...";
            BloggingContext bc = new BloggingContext();
            bc.Database.EnsureCreated();
            bc.Dispose();
            myButton.Content = "Clicked";
        }
    }

    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        public string DbPath { get; }

        public BloggingContext()
        {
            //string path = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
            string path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); 
            Debug.WriteLine($"path: {path}");
            this.DbPath = System.IO.Path.Join(path, "blogging.db");
        }

        // The following configures EF to create a Sqlite database file in the
        // special "local" folder for your platform.
        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlite($"Data Source={this.DbPath}");
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }

        public List<Post> Posts { get; } = new();
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public Blog Blog { get; set; }
    }
}

[sjb-sjb]

A related question. If I use FileSavePicker.PickSaveFileAsync to let the user pick the file location, then it returns a StorageFile. Will SQLite be able to create the database using the path and filename from that StorageFile? I am not very clear on the exact status of the file indicated by the StorageFile returned from the picker and how that will interact with SQLite.

[sjb-sjb]

When I moved the simplified BloggingContext to my app and tried the same MyButton_Click as above, it gave SQLite error 14, could not open file. So maybe something is off with my installation or my package.

[roji]

@sjb-sjb trying to read the above, there's a lot going on and it's not clear exactly what problem you have (or whether it's related to EF Core).

Please try to submit a simple, runnable console program - without any code that is isn't needed, or Winforms, or anything - and post the full exception and stack trace you are seeing.

[sjb-sjb]

OK. Did some experimenting early this morning. Using VS 2022 Community Edition, WindowsAppSDK installed, workloads for .NET desktop development and UWP development. Details per item 5 in the above posting. I am executing as a normal user, not as an Administrator.

Steps to reproduce:

1. Create a new solution using the "Blank App, Packaged (WinUI 3 in Desktop)" template.

2. Manage NuGet packages for solution and add Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.Sqlite 6.0.0. Observe that Microsoft.Windows.SDK.BuildTools 10.0.22000.194 and Microsoft.WindowsAppSDK 1.0.0 are already loaded.

3. Add the following lines in myButton_Click in MainWindow.xaml.cs, with the relevant #includes:

        Debug.WriteLine("Options");
        DbContextOptionsBuilder<DbContext> dbOptionsBuilder = new DbContextOptionsBuilder<DbContext>();
        string filepath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
        dbOptionsBuilder.UseSqlite($"Data Source={Path.Join(filepath, "context.db")}"); // was: ("Filename=" + this.GetFilename());
        Debug.WriteLine("New");
        using (var context = new DbContext(dbOptionsBuilder.Options)) {
            Debug.WriteLine("Create");
            context.Database.EnsureCreated(); // was: await context.Database.MigrateAsync(); 
            Debug.WriteLine("Dispose.");
        }
        Debug.WriteLine($"Done");
   

4. Compile and run. Go to the Exceptions Settings window and select all of the exceptions. In File Explorer, browse to [user]\AppData\Local\Packages[package name]\LocalCache\Local and observe that it is empty. Click the button and observe a break on SQLite error 14, cannot open file (exception stack is shown below) during the EnsureCreated call. There are also exceptions noted on the Output/Immediate window per above posting items 1 and 2. Continue past the exceptions. Go back to File Explorer and observe one file named context.db. If you were to click the button again there would be no exceptions, presumably because the database already exists. Exit the app using the X exit in upper right-hand corner and observe an exception saying "The given object has already been closed/disposed and may no longer be used." Continue past the exception. Go back to File Explorer and observe that there are now three files: context.db, context.db-shm and context.db-wal.

5. Delete the three files and re-run the program but do not click the button. When you exit the ending breaks on exception still occur. The exception is: Exception thrown at 0x7642B922 (KernelBase.dll) in TestApp.exe: WinRT originate error - 0x80000013 : 'The given object has already been closed / disposed and may no longer be used.'. There are no files produced in AppData[package]\LocalCache\Local. The stack trace is shown below.

6. Now create another solution but use the "Console App" template ("A project for creating a command-line application that can run on .NET on Windows, Linux and macOS"). Paste the code into the main proc in Program.cs. In File Explorer, browse to [user]\AppData\Local instead of the package directory. Remember to check on all exceptions in the Exceptions Settings window. Observe break on Sqlite Error 14. Exit the program; the ending three exceptions do not occur. There is only one file in the Local folder, context.db.

Stack trace for Sqlite error 14

 	Microsoft.Data.Sqlite.dll!Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int rc, SQLitePCL.sqlite3 db)	Unknown
	Microsoft.Data.Sqlite.dll!Microsoft.Data.Sqlite.SqliteConnectionInternal.SqliteConnectionInternal(Microsoft.Data.Sqlite.SqliteConnectionStringBuilder connectionOptions, Microsoft.Data.Sqlite.SqliteConnectionPool pool)	Unknown
 	Microsoft.Data.Sqlite.dll!Microsoft.Data.Sqlite.SqliteConnectionFactory.GetConnection(Microsoft.Data.Sqlite.SqliteConnection outerConnection)	Unknown
 	Microsoft.Data.Sqlite.dll!Microsoft.Data.Sqlite.SqliteConnection.Open()	Unknown
 	Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(bool errorsExpected)	Unknown
 	Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(bool errorsExpected)	Unknown
 	Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(bool errorsExpected)	Unknown
 	Microsoft.EntityFrameworkCore.Sqlite.dll!Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteDatabaseCreator.Exists()	Unknown
 	Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()	Unknown
 	Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()	Unknown
>	TestApp.dll!TestApp.MainWindow.MyButton_Click(object sender, Microsoft.UI.Xaml.RoutedEventArgs e) Line 44	C#
 	Microsoft.WinUI.dll!WinRT._EventSource_global__Microsoft_UI_Xaml_RoutedEventHandler.EventState.GetEventInvoke.AnonymousMethod__1_0(object sender, Microsoft.UI.Xaml.RoutedEventArgs e)	Unknown
 	Microsoft.WinUI.dll!ABI.Microsoft.UI.Xaml.RoutedEventHandler.Do_Abi_Invoke.AnonymousMethod__0(Microsoft.UI.Xaml.RoutedEventHandler invoke)	Unknown
 	Microsoft.WinUI.dll!ABI.Microsoft.UI.Xaml.RoutedEventHandler.Do_Abi_Invoke(System.IntPtr thisPtr, System.IntPtr sender, System.IntPtr e)	Unknown
 	[Native to Managed Transition]	
 	Microsoft.ui.xaml.dll!0bc20ce3()	Unknown
.... additional stack frames omitted....

Stack trace for ending exception

KernelBase.dll!7642b922()
KernelBase.dll![Frames below may be incorrect and/or missing, no symbols loaded for KernelBase.dll]
combase.dll!75c9c64e()
combase.dll!75baec1e()
ntdll.dll!77c930cc()
KernelBase.dll!763fd40e()
marshal.dll!1559c3e8()
marshal.dll!1559c06b()
Microsoft.UI.Input.dll!165a83fe()
Microsoft.UI.Input.dll!165a85b8()
Microsoft.UI.Input.dll!1656a6ac()
Microsoft.UI.Input.dll!16564ae5()
Microsoft.UI.Input.dll!16564c86()
Microsoft.UI.Input.dll!16564ea0()
Microsoft.UI.Input.dll!16564e0f()
Microsoft.UI.Input.dll!1655f80c()
Microsoft.UI.Input.dll!1655ef1e()
Microsoft.UI.Input.dll!165604b7()
Microsoft.UI.Input.dll!16564ae5()
Microsoft.UI.Input.dll!16564c86()
Microsoft.UI.Input.dll!1655f540()
Microsoft.UI.Input.dll!165a2c7d()
Microsoft.UI.Input.dll!16564b3e()
Microsoft.UI.Input.dll!16564c86()
Microsoft.UI.Input.dll!16564ea0()
Microsoft.UI.Input.dll!16564e0f()
Microsoft.UI.Input.dll!1659eaa1()
Microsoft.UI.Input.dll!1659ddfd()
Microsoft.UI.Input.dll!1659dd62()
Microsoft.UI.Input.dll!1659ac20()
Microsoft.ui.xaml.dll!79dff9f2()
Microsoft.ui.xaml.dll!79f08a3c()
Microsoft.ui.xaml.dll!79f077f2()
Microsoft.ui.xaml.dll!79f07f6d()
Microsoft.ui.xaml.dll!79944b8f()
user32.dll!770e107b()
user32.dll!770d802a()
user32.dll!770d7707()
user32.dll!770d58ab()
Microsoft.UI.Input.dll!1659f54a()
Microsoft.UI.Input.dll!1659f9f1()
Microsoft.UI.Input.dll!16597cc8()
Microsoft.UI.Input.dll!16597b62()
Microsoft.UI.Input.dll!1659f9f1()
Microsoft.UI.Input.dll!1659f8eb()
user32.dll!770e107b()
user32.dll!770d802a()
user32.dll!770d7c2a()
user32.dll!770db7ef()
ntdll.dll!77c94e7d()
win32u.dll!75f1107c()
user32.dll!770d6496()
user32.dll!770d59c9()
uxtheme.dll!73f63665()
uxtheme.dll!73f5fb91()
uxtheme.dll!73f5f808()
user32.dll!770d61e7()
Microsoft.ui.xaml.dll!79944eb5()
Microsoft.ui.xaml.dll!79944ca6()
Microsoft.ui.xaml.dll!79944b8f()
user32.dll!770e107b()
user32.dll!770d802a()
user32.dll!770d7707()
user32.dll!770d58ab()
Microsoft.UI.Input.dll!1659f54a()
Microsoft.UI.Input.dll!1659f9f1()
Microsoft.UI.Input.dll!16597cc8()
Microsoft.UI.Input.dll!16597b62()
Microsoft.UI.Input.dll!1659f9f1()
Microsoft.UI.Input.dll!1659f8eb()
user32.dll!770e107b()
user32.dll!770d802a()
user32.dll!770d7c2a()
user32.dll!770db7ef()
ntdll.dll!77c94e7d()
user32.dll!770d6496()
user32.dll!770d59c9()
uxtheme.dll!73f63665()
uxtheme.dll!73f5fb91()
uxtheme.dll!73f5f808()
user32.dll!770d61e7()
Microsoft.ui.xaml.dll!79944b8f()
Microsoft.ui.xaml.dll!79944b8f()
user32.dll!770e107b()
user32.dll!770d802a()
user32.dll!770d7707()
user32.dll!770d58ab()
Microsoft.UI.Input.dll!1659f54a()
Microsoft.UI.Input.dll!1659f9f1()
Microsoft.UI.Input.dll!16597cc8()
Microsoft.UI.Input.dll!16597b62()
Microsoft.UI.Input.dll!1659f9f1()
Microsoft.UI.Input.dll!1659f8eb()
user32.dll!770e107b()
user32.dll!770d802a()
user32.dll!770d7c2a()
user32.dll!770d5b60()
Microsoft.ui.xaml.dll!79969871()
Microsoft.ui.xaml.dll!79968c56()
[Managed to Native Transition]
Microsoft.WinUI.dll!ABI.Microsoft.UI.Xaml.IApplicationStatics.Microsoft.UI.Xaml.IApplicationStatics.Start(Microsoft.UI.Xaml.ApplicationInitializationCallback callback)
Microsoft.WinUI.dll!Microsoft.UI.Xaml.Application.Start(Microsoft.UI.Xaml.ApplicationInitializationCallback callback)
TestApp.dll!TestApp.Program.Main(string[] args) Line 31
	at C:\Users\steve\OneDrive\Documents\VS Projects\TestApp\TestApp\obj\x86\Debug\net6.0-windows10.0.19041.0\win10-x86\App.g.i.cs(31)
[Native to Managed Transition]
hostpolicy.dll!504b6374()
hostpolicy.dll!504d237f()
hostpolicy.dll!504b5b66()
hostfxr.dll!50509aa3()
hostfxr.dll!5050b8b9()
hostfxr.dll!5050d544()
hostfxr.dll!5050bc48()
hostfxr.dll!50506eeb()
TestApp.exe!00c4ee7a()
TestApp.exe!00c4f1ba()
TestApp.exe!00c502ec()
kernel32.dll!7616fa29()
ntdll.dll!77c87a7e()
ntdll.dll!77c87a4e()

[sjb-sjb]

I have opened an issue 6817 in microsoft-ui-xaml in respect of the exceptions that happen when you exit the program. These do not seem to be related to EF Core given that they occur even if EF Core is not referenced. The issue in this posting (#27580) is the SQLite error 14 and the fact that three files instead of 1 are left over after exiting. I suppose that the three exceptions reporting in 6817 could be an indirect cause of having 3 files left over, if they prevent SQLite from cleaning up.

[ajcvickers]

three files instead of 1 are left over after exiting.

This was fixed in 6.0.1--see #26422. Please make sure to always use the latest patch version of a release.

[sjb-sjb]

OK !! Thanks for the tip.
I did try it with 6.0.2 at one point, but since I still got SQLite Error 14, I assumed the issue was the same.
So... about the error 14, I guess I should just ignore that -- ? It looks like it is propagating into Microsoft.EntityFrameworkCore.Relational.dll but not past there.

[sjb-sjb]

Catching up with the fact that this was discussed in microsoft/WindowsAppSDK#8

[ajcvickers]

Note from triage: issues with StorageFile are well-known. We don't intend to make any changes in EF for this.

[sjb-sjb]

See #30642