Xamarin - Entity Framework - SqLite - GetItemsAsync : unable to open database file

Question

In Visual studio 2017, I created a Cross-Platform Mobile App (Xamarin.Forms)

• NETStandard 2.0
• Microsoft.Data.Sqlite.Core 2.2.0
• Microsoft.EntityFrameworkCore 2.2.0
• Microsoft.EntityFrameworkCore.Sqlite 2.2.0
• Microsoft.EntityFrameworkCore.Tools 2.2.0

I did a code first migration to Sqlite with Entity Framework following the advice of Mark Smith. All tables are created successfully. The database was seeded.

When I try to get the ItemsViewModel (see code below), I get the following exception (after 20 seconds) on the line --> List item list1 = await db.Person.ToListAsync();

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 14: 'unable to open database file'. at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) at Microsoft.Data.Sqlite.SqliteConnection.Open() at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)

Strangely enough my test method TestSqLiteDataStoreAsync (see code below) correctly returns the expected list of items without causing an error.

Also the fact that it takes 20 seconds to get the error message indicates that it was able to find the database. When you have a wrong db Name/path the error pops immediately.

Below you find the relevant code. If you need anything more, let me know. Any help on how to continue debugging is also welcome. Or has anyone a basic working UWP code example I can compare mine with ? I have added a solution on Github called 'temp' with the needed database. If one might have the time to try to reproduce the error :) Thx.

[XamlCompilation(XamlCompilationOptions.Compile)]
public partial class ItemsPage : ContentPage
{
    ItemsViewModel viewModel;

    public ItemsPage()
    {
        InitializeComponent();
        //1 code starts here
        BindingContext = viewModel = new ItemsViewModel();
    }

    async void OnItemSelected(object sender, SelectedItemChangedEventArgs args)
    {
        var item = args.SelectedItem as Item;
        if (item == null)
            return;

        await Navigation.PushAsync(new ItemDetailPage(new ItemDetailViewModel(item)));

        ItemsListView.SelectedItem = null;
    }

    async void AddItem_Clicked(object sender, EventArgs e)
    {
        await Navigation.PushModalAsync(new NavigationPage(new NewItemPage()));
    }


    //adding 'async' did not solve the problem
    protected async override void OnAppearing()
    {
        base.OnAppearing();

          if (viewModel.Items.Count == 0)
            //2 code continues here 
            viewModel.LoadItemsCommand.Execute(null);
    }
}


public class ItemsViewModel : BaseViewModel
{
    public ObservableCollection<Item> Items { get; set; }
    public Command LoadItemsCommand { get; set; }

    public ItemsViewModel()
    {
        Title = "Browse";
        Items = new ObservableCollection<Item>();

        //3 code continues here 
        LoadItemsCommand = new Command(async () => await ExecuteLoadItemsCommand());

        MessagingCenter.Subscribe<NewItemPage, Item>(this, "AddItem", async (obj, item) =>
        {
            var newItem = item as Item;
            Items.Add(newItem);
            await DataStore.AddItemAsync(newItem);
        });

    }

    //My test method
    public async Task<List<Item>> TestSqLiteDataStoreAsync()
    {
        SqLiteDataStore SqLiteDataStore = new SqLiteDataStore();
         return await SqLiteDataStore.GetItemsAsync(false);
    }



    async Task ExecuteLoadItemsCommand()
    {
        if (IsBusy)
            return;

        IsBusy = true;

        try
        {
            Items.Clear();
            //4 code continues here 
            var items = await DataStore.GetItemsAsync(true);
            foreach (var item in items)
            {
                Items.Add(item);
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex);
        }
        finally
        {
            IsBusy = false;
        }
    }
}



public class SqLiteDataStore : IDataStore<Item>
{
    public string dbFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
    public string fileName = "TestSqLite.db";

    public SqLiteDataStore()  { }

    public async Task<List<Item>> GetItemsAsync(bool forceRefresh = false)
    {
        try
        {
            using (var db = new Models.MyContext(Path.Combine(dbFolder, fileName)))
            {
                List<Person> list1 = new List<Person>();
                List<Item> list2 = new List<Item>();

                //5 I get the error here
                list1 = await db.Person.ToListAsync();

                foreach (Person person in list1)
                {
                    Item item = new Item(person);
                    list2.Add(item);
                }
                return list2;
            }
        }
        catch (Exception)
        {
            throw;
        }
     }


    public async Task<bool> AddItemAsync(Item item)
    {
       throw new NotImplementedException();
    }

    public Task<bool> UpdateItemAsync(Item item)
    {
        throw new NotImplementedException();
    }

    public Task<bool> DeleteItemAsync(string id)
    {
        throw new NotImplementedException();
    }

    public Task<Item> GetItemAsync(string id)
    {
        throw new NotImplementedException();
    }
}

Answer 1

As we had mentioned before, simply replacing Environment.SpecialFolder.Personal with Environment.SpecialFolder.LocalApplicationData will solve the problem. You can check Environment.SpecialFolder Enum for details about each option. And now your items are loading properly:

---

Answering your follow-up comments:

Yes, the MCVE you posted on github now runs without any problems after changing to LoadApplicationData like we described, including your test code, as shown below:

[3/10/2019 4:26:28 PM Informational] [xUnit.net 00:00:00.02] xUnit.net VSTest Adapter v2.4.0 (64-bit .NET Core 4.6.27129.04)
[3/10/2019 4:26:31 PM Informational] [xUnit.net 00:00:03.41]   Discovering: XUnitTestProject1
[3/10/2019 4:26:31 PM Informational] [xUnit.net 00:00:03.61]   Discovered:  XUnitTestProject1
[3/10/2019 4:26:31 PM Informational] ========== Discover test finished: 1 found (0:00:11.5651246) ==========
[3/10/2019 4:26:31 PM Informational] ------ Run test started ------
[3/10/2019 4:26:32 PM Informational] [xUnit.net 00:00:00.00] xUnit.net VSTest Adapter v2.4.0 (64-bit .NET Core 4.6.27129.04)
[3/10/2019 4:26:33 PM Informational] [xUnit.net 00:00:00.63]   Discovering: XUnitTestProject1
[3/10/2019 4:26:33 PM Informational] [xUnit.net 00:00:00.70]   Discovered:  XUnitTestProject1
[3/10/2019 4:26:33 PM Informational] [xUnit.net 00:00:00.70]   Starting:    XUnitTestProject1
[3/10/2019 4:26:35 PM Informational] [xUnit.net 00:00:02.60]   Finished:    XUnitTestProject1
[3/10/2019 4:26:35 PM Informational] ========== Run test finished: 1 run (0:00:03.9952011) ==========

If we switch back to Personal folder, we get the same exception you reported:

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 14: 'unable to open database file'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteConnection.Open()
   at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)

The reason why you can run your test code but not the app is probably because these two have different permission requirements.

Please remember that for this to work in both cases, you have not only to change Environment.SpecialFolder, but also, place TestSqLite.db in the corresponding folder. In our case the folder is something like this:

C:\Users\SomeUser\AppData\Local\Packages\0eea1ae6-65c3-42e9-9a50-869730e52f63_2ft3g98yvwh70\LocalState

Obviously you should get a similar but slightly different path.

---

Comments on result verification: It takes only a couple minutes to perform this simple verification procedure. Here's what happens when we switch back to Environment.SpecialFolder.Personal, having TestSqLite.db in its corresponding C:\Users\SomeUser\Documents folder.

As depicted below, the app takes a longer than normal time (cut short here to limit upload size) to load the initial Browse page. When it finally does, the initial page is empty. Switching between the About page and then back to Browse page causes the app to hang (wait for it). Again, this is a simple procedure that takes little time and no effort to perform.

Answer 2

According to get the

"Unable to open database file"

error maybe you're pointing the code at a file that doesn't exist.

A "Table not found" error means it found the database, but not the table you were looking for. On the other hand, "Unable to open database file" means that it couldn't even find the database, and didn't even bother looking for a table. You're much closer to it working correctly when you're getting "Table not found".