Xamarin.Native Tutorial: InvoiceJe: Entity Framework + SQLite Database: Setup

Xamarin.Native Tutorial: InvoiceJe: Entity Framework + SQLite Database: Setup

(Click here to view the table of contents of this Xamarin.Native tutorial)

Alright, now we're going to implement Entity Framework + SQLite database inside our app (finally!).

What will happen is that we will update our Repository class to initialize with a path to the database file. Now, since each platform saves their database in different places, we will have to define the database path for each platform.

Then, we will simply implement the CRUD operations to the Edit page and the Create page.

Yay, let's go!

We will be referencing these articles: 

Update to .NET Standard 1.3

First, to use EntityFramework inside our mobile apps, we'll need to update our PCL to .NET Standard 1.3.

Note: Don't try any later versions, it just doesn't work well with this project for some reason.

So, go to Solution Explorer > PCL > Properties:

Click on the Target .NET Platform Standard link.

Your project will reload. Then, open Properties again. This time, set the target to .NET Standard 1.3.

Install EntityFramework and SQLite

Next, install Microsoft.EntityFrameworkCore (1.1.2) and Microsoft.EntityFrameworkCore.Sqlite (1.1.2) in each project.

Do NOT install 2.0.0. We want version 1.1.2.

Alright, now we're talking!

Create DataContext

Now, I will assume you guys are comfortable with Entity Framework. So, I'll skip explaining what's Entity Framework is.

Let's add a DataContext class that inherits from DbContext.

Inside the PCL Project > Data, create a class named DataContext and replace it with the following code:

using InvoiceJe.Models;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

namespace InvoiceJe.Data
{
    public class DataContext : DbContext
    {

        private string _databasePath = "";

        public DataContext() : base() 
        {

        }

        public DataContext(string databasePath)
        {

            _databasePath = databasePath;
            Database.Migrate();
            //Database.EnsureCreated();

            //// Android
            //var dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "invoiceje.db");

            //// UWP
            //var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "exrin.db");

            //// iOS
            //var dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "..", "Library", "exrin.db");

        }

        public DbSet<Invoice> Invoices { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

            string connectionStringBuilder = new SqliteConnectionStringBuilder()
            {
                DataSource = _databasePath
            }
            .ToString();

            var connection = new SqliteConnection(connectionStringBuilder);
            optionsBuilder.UseSqlite(connection);
        }
    }
}

Update Repository.cs

Now, we're going to update our Repository class to use EntityFramework and query from database rather than acting as a dummy all this while.

So, open Repository.cs inside the PCL and replace it with this:

using InvoiceJe.Models;
using System.Collections.Generic;
using System.Linq;

namespace InvoiceJe.Data
{
    public class Repository
    {

        private DataContext _db;

        public Repository(string databasePath)
        {
            _db = new DataContext(databasePath);
        }

        public async System.Threading.Tasks.Task CreateAsync(Invoice invoice)
        {
            _db.Invoices.Add(invoice);
            await _db.SaveChangesAsync();
        }

        public async System.Threading.Tasks.Task UpdateAsync(Invoice invoice)
        {
            _db.Entry(invoice).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            await _db.SaveChangesAsync();
        }

        public IEnumerable<Invoice> GetInvoices()
        {
            return _db.Invoices.ToList();
        }
    }
}

(We purposely didn't change GetInvoices() to Async so that other usage of GetInvoices() wouldn't break. However, feel free to change this later)

Create FileAccessHelper in Each Platform

Next thing we're going to do is that we're going to make a FileAccessHelper class in each platform. These classes will help us define the path to save our database in each platform. We need to define in each class because each platform saves data in different places.

Inside the Android project, under the folder Extensions, add this class:

namespace InvoiceJe.Droid.Extensions
{
    public class FileAccessHelper
    {
        public static string GetLocalFilePath(string filename)
        {
            // Use the SpecialFolder enum to get the Personal folder on the Android file system.
            // Storing the database here is a best practice.
            string path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            return System.IO.Path.Combine(path, filename);
        }

        public static string GetLocalDatabasePath()
        {
            return GetLocalFilePath("invoiceje.db");
        }

    }
}

Next, inside the iOS project, add a folder named Extensions and add this class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Foundation;
using UIKit;

namespace InvoiceJe.iOS.Extensions
{
    public class FileAccessHelper
    {
        public static string GetLocalFilePath(string filename)
        {
            // Use the SpecialFolder enum to get the Personal folder on the iOS file system.
            // Then get or create the Library folder within this personal folder.
            // Storing the database here is a best practice.
            var docFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            var libFolder = System.IO.Path.Combine(docFolder, "..", "Library");

            if (!System.IO.Directory.Exists(libFolder))
            {
                System.IO.Directory.CreateDirectory(libFolder);
            }

            return System.IO.Path.Combine(libFolder, filename);
        }

        public static string GetLocalDatabasePath()
        {
            //var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "exrin.db");
            var dbPath = GetLocalFilePath("exrin.db");
            return dbPath;
        }
    }
}

..And similarly in the UWP project, add a folder named Extensions and add this class:

using System.IO;

namespace InvoiceJe.UWP.Extensions
{
    public class FileAccessHelper
    {
        public static string GetLocalFilePath(string filename)
        {
            // For UWP, we store the database file in our application data's local folder.
            var path = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
            return Path.Combine(path, filename);
        }

        public static string GetLocalDatabasePath()
        {
            //var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "exrin.db");
            var dbPath = GetLocalFilePath("invoiceje.db");
            return dbPath;
        }
    }
}

...Alright.

Update Each Line Where We Called Repository

Now, unfortunately a vanilla Xamarin.Native doesn't have a built-in dependency injection (nor does this tutorial will cover it), so we'll have to do this the manual way.

Since we have changed our Repository construction to have a string parameter, we'll need to change every line in each of our project that references Repository. So, open Error List and you'll see all instance where Repository is used inside our projects. 

Change all of the lines...

var repository = new Repository();

... to this:

var repository = new Repository(FileAccessHelper.GetLocalDatabasePath());

To make things simple, I'll list down the files you'll need to change:

Android: InvoicesFragment, InvoicesEditActivity

iOS: InvoicesTableViewController, InvoicesEditTableViewController

UWP: MainPage.xaml.cs, InvoicesEditPage.xaml.cs

"Really, purr?"

"Ya rly"

"Isn't there a way to make this, not horrible? What if later I want to change the database name?"

 Yup, the smarties amongst y'all would probably have thought of some ways to do that. We ain't gonna go through that in this step, for clarity. But later we'll be writing on some of my opinions on how to centralize these codes. K?

Alright, now that we're done setting up our repository, we only have 2 major things to care about.

  1. EntityFramework Migrations
  2. iOS Xamarin Linker File (IMPORTANT! Or else your app will throw an error when it runs on iOS devices)

EntityFramework Migrations

Now, we're going to implement migrations for our SQLite database using Entity Framework.

Unfortunately, unlike ASP.NET, as of the time of writing, there's no built-in tool where you can simply run the command 'Enable-Migration' or 'Add-Migration xxx' inside the Package Manager Console.

We'll need to use the dotnet command line tool instead to achieve this. However, we can't use it in our PCL library. So, we'll need a workaround.

What's going to happen is that:

  1. We're going to create a new .NET Core console application
  2. Install Microsoft.EntityFramework tools and library in the console app
  3. We'll copy over our DataContext class to the .NET Core console application
  4. We'll run the dotnet Add Migration command to add the migration files
  5. Then, we copy over the migration files to our PCL
  6. Done!

We'll be referring the Xamarin blog heavily: https://blog.xamarin.com/building-android-apps-with-entity-framework/

So let's go.

InvoiceJeMigrationsBait .NET Core Console App

In Solution Explorer, Right click on "Solution 'InvoiceJe'" and Add New Project.

Select .NET Core > Console App. Name it InvoiceJeMigrationsBait.

Next, install Microsoft.EntityFrameworkCoreMicrosoft.EntityFrameworkCore.Sqlite and Microsoft.EntityFrameworkCore.Design

...inside InvoiceJeMigrationsBait via NuGET.

REMEMBER. Do NOT install 2.0.0. We want version 1.1.2.

Install EntityFramework Tools and Library

Next, we need to install Microsoft.EntityFrameworkCore.Tools.DotNet.

However, if we try to install this package via NuGET we'll get an error "Package 'Microsoft.EntityFrameworkCore.Tools.DotNet 1.0.0' has a package type 'DotnetCliTool' that is not supported by project", which is discussed in this EntityFramework issue on Github.

To overcome this, we can edit the InvoiceJeMigrationsBait .csproj file. To edit it, simply right-click on InvoiceJeMigrationsBait project. Unload project. And then right-click again and click on the edit InvoiceJeMigrationsBait .csproj. 

Then, add this tag:

<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0" />

...inside the <ItemGroup> tag.

Afterwards, Nuget will install the DotNetCliTool for you. Let Nuget restore the packages and you should be good to go. 

Your .csproj file should look like this once you have installed everything:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp1.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SQLite" Version="1.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="1.1.2" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0" />
  </ItemGroup>

</Project>

Copy DataContext class to InvoiceJeMigrationsBait

Now, simply copy the DataContext class and the Models (in our case, Invoice.cs) to InvoiceJeMigrationsBait project. What I did was I simply copied the whole Data folder and Models folder.

Add Migration Using dotnet Tool

Now we're going to add our first initial migration for our project. We're going to use the dotnet command line interface to achieve this, since we can't use our Package Manager Console.

Now, open Command Prompt, navigate to the InvoiceJeMigrationsBait folder. Then, run this command: 

dotnet ef migrations add Initial

This is equivalent to "Add-Migrations Initial" if we use Package Manager Console.

If things go right, the dotnet CLI will generate the Migrations folder and classes for you. Your InvoiceJeMigrationsBait project should look like this:

Copy over the migration files to our PCL

Now, simply copy over the Migrations folder to our PCL. Remember, our whole exercise here is to generate the Migrations file for EntityFramework in our PCL.

Alright, now we're done with setting up EF Migrations.

Add Xamarin Linker File for iOS Project

Now, according to Github issue #7158, even though now using EntityFramework with iOS emulators work, there are still problems when running it on iOS physical devices, which is due to the fact that Xamarin linker removes certain types and methods that is not directly referenced in our project.

The answer is to create our own Xamarin linker file and manually tell Xamarin to include those methods. (Thanks to cwrea!)

So, in the root of our iOS project, add an XML file named Linker.xml. Then, replace the contents with this:

<?xml version="1.0" encoding="utf-8" ?>
<linker>
  <!--
  
        LinkDescription.xml
        
        Prevents runtime errors when reflection is used for certain types that are not otherwise referenced
        directly in the project, and that would be removed by the Xamarin linker.
        
        These kinds of runtime errors do occur in particular when using Entity Framework Core on iOS. EF Core's
        query parser does reference certain .NET methods _only_ via reflection, and it is those reflection-only
        instances that we need to guard against by ensuring the linker includes them. For the curious, search
        for mentions of "GetRuntimeMethod" at https://github.com/aspnet/EntityFramework. Use of EF Core more
        advanced than this sample may require additional types/methods added to those below.
        
        Include the following in the project build settings under "Additional mtouch arguments":
          [hyphen][hyphen]xml=${ProjectDir}/LinkDescription.xml
          
        There is supposed to be a "LinkDescription" build action for this linker config file so the step above
        shouldn't be necessary, but at time of writing Visual Studio 2017 for PC doesn't show that build action
        in iOS projects, even though it is an option within iOS projects on Visual Studio 2017 for Mac.
        
  -->
  <assembly fullname="mscorlib">
    <type fullname="System.String">
      <method name="Compare"></method>
      <method name="CompareTo"></method>
      <method name="ToUpper"></method>
      <method name="ToLower"></method>
    </type>
  </assembly>
  <assembly fullname="System.Core">
    <type fullname="System.Linq.Expressions.Expression`1"></type>
    <type fullname="System.Linq.Queryable"></type>
  </assembly>
</linker>

...Finally...

...Alright, now that we've set up everything, try running the app on each platform.

Yeah, currently it'll be empty simply because we didn't add anything inside our database yet. So, let's do it.