UWP: Deployment of a project that uses SQLite and Dapper

To be able to use SQLite in my UWP project, I added Nuget packages System.Data.SQLite.Core 1.0.111 and Dapper 1.60.6. It builds and runs perfectly. However, when I tried to deploy this project to Store I get those errors:

Windows security features test

File C:\myApp\sni.dll has failed the AppContainerCheck check.
File C:\myApp\SQLite.Interop.dll has failed the AppContainerCheck check.

Supported API test

API CryptDuplicateKey in advapi32.dll is not supported for this application type. SQLite.Interop.dll calls this API.
API AreFileApisANSI in kernel32.dll is not supported for this application type. SQLite.Interop.dll calls this API.
...

I checked "Compile with native toolchain" option and built it on release configuration as suggested in Windows App Certification Kit and another stackoverflow answer, but the problem persists.

Also in Windows App Certification Kit Test Results, it says

"Apply the required linker options - SAFESEH, DYNAMICBASE, NXCOMPAT, and APPCONTAINER - when you link the app."

I'm using VS 2019 and given that this project is written in c# (not c++), I'm not sure linker options is even applicaple for my case.

Another strange thing is, unlike x86 and x64 packages, there is no problem deploying arm package.

Is this problem fixable, or should I stop using those nuget packages altogether.

Solution

It seems that it is not possible for now. So let me write other possible solutions and my experience with them.

1 - Using Microsoft.Data.SQLite package

As Xavier Xie suggested in the comment section, you can use this guide in MSDN. It is kind of vanilla library for SQLite usage in a UWP app.

I didn't prefer this for a few reasons. In my opinion it is kind of wordy in syntax and not that trivial to get started. You need to be careful about versions of packages you install. More importantly, in my case, you need to change your read and update functions for a class as fields of that class changes. Thus it is harder to maintain than when you use higher level libraries like Dapper.

2 - Use Entity Framework

Using entity framework is simpler to maintain, but I think it is hard to get started. I already had a database in which case documentation is not that helpful.

3 - Use a different method than SQLite for data storage

Because I have some initial data and it is not supposed to get so big, I preferred to use json files to store and update them.

I used Newtonsoft Nuget Package for serialising, deserialising classes, and used ApplicationData Class to store data. I thing both of them are very simple to use and well documented with clear examples.

Here is some parts of code from what I did:

    const string myDataFilename= "myData.json";
    const string backupFolderPath = "ms-appx:///DataModel/";

    async Task LoadData()
    {
        string json = await StorageApi.ReadFromFile(myDataFilename, backupFolderPath + myClassFilename);
        try
        {
            myList = JsonConvert.DeserializeObject<List<myClass>>(json);
        }
        catch
        {
            // maybe do some reset logic here
            await StorageApi.CopyFile(backupFolderPath + myDataFilename, myDataFilename);
            await LoadData();
        }
    }
    
    async public void saveData()
    {
        string json = JsonConvert.SerializeObject(myList);
        await StorageApi.WriteToFile(myDataFilename, json);
    }

And I wrote a wrapper class for using ApplicationData class In case links goes dead, here are some code from it:

    public static async Task WriteToFile(string relativePath, string data)
    {
        StorageFile sampleFile = await localFolder.CreateFileAsync(relativePath,
            CreationCollisionOption.ReplaceExisting);
        await FileIO.WriteTextAsync(sampleFile, data);
    }

    // Read data from a file
    public static async Task<string> ReadFromFile(string relativePath, string backupPath = "")
    {
        try
        {
            StorageFile sampleFile = await localFolder.GetFileAsync(relativePath);
            return await FileIO.ReadTextAsync(sampleFile);
        }
        catch (FileNotFoundException e)
        {
            Debug.WriteLine( "Relative path: {0}, backupPath: {1}, Error str: {2}", relativePath, backupPath, e.Message);

            if (backupPath == "")
                return "";
            else
            {
                await CopyFile(backupPath, relativePath);
                return await ReadFromFile(relativePath);
            }
        }
        catch (IOException e)
        {
            Debug.WriteLine(e.Message);
        }

        return "";
    }

    public static async Task CopyFile(string src, string relativeDst)
    {
        try
        {
            StorageFile file = await StorageFile.GetFileFromApplicationUriAsync(
                new Uri(src));
            await file.CopyAsync(localFolder, relativeDst, NameCollisionOption.ReplaceExisting);
        }
        catch (FileNotFoundException e)
        {
            Debug.WriteLine(e.Message);
        }
        catch (IOException e)
        {
            Debug.WriteLine(e.Message);
        }
    }

Note: I will choose this answer as accepted for now. If a better answer that solves the exact problem comes up, I will accept it instead.