Foreign keys in local databases with SQLite-net and .NET MAUI

This publication is part of the .NET MAUI Advent Calendar 2022, an initiative led by Héctor Pérez and Luis Beltrán. Check this link for more interesting .NET MAUI articles posted by community members.

A common misconception about SQLite-net library is that it can't handle Foreign Keys in a local SQLite database. Actually, they are supported! Maybe not in the way you'd expect it, but it works.

Let's see how to develop a .NET MAUI app that stores information in a local database with 2 tables referenced by a foreign key! In our application, we can perform CRUD operations over two tables: Departments and Employees. An employee belongs to one department, and the department includes many employees (so there is a 1-N relationship).

Source code available here.

Step 1. Create the following project structure:

Step 2. Add the following Nuget packages:

• sqlite-net-pcl
• SQLitePCLRaw.bundle_green
• SQLitePCLRaw.core
• SQLitePCLRaw.provider.dynamic_cdecl
• SQLitePCLRaw.provider.sqlite3
• CommunityToolkit.Mvvm (optional, but recommended)

Step 3. In Helpers folder, create a Constants class with the following code that includes public constants for:

• the local database filename
• the two table names
• two Create Table statements with the table specification. You can observe that Employees include a foreign key restriction.
• two Select queries that return data from the tables. The second query is a JOIN to demonstrate that the relationship actually exists.
• two routes (for navigation using Shell)

namespace DemoFK.Helpers
{
    public static class Constants
    {
        public const string LocalDbFile = "company_v01.db";
        public const string DepartmentTable = "departments";
        public const string EmployeeTable = "employees";

        public const string CreateDepartmentTableStatement = $"CREATE TABLE IF NOT EXISTS {DepartmentTable} (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(255));";
        public const string CreateEmployeeTableStatement = $"CREATE TABLE IF NOT EXISTS {EmployeeTable} (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(255), DepartmentId INT, FOREIGN KEY(DepartmentId) REFERENCES departments(Id));";

        public const string AllDepartmentsQuery = $"SELECT * FROM {DepartmentTable}";
        public const string EmployeesAndDepartmentQuery = $"SELECT e.*, d.Name as DepartmentName FROM {EmployeeTable} e JOIN {DepartmentTable} d ON e.DepartmentId = d.Id";

        public const string DepartmentDetailsRoute = "DepartmentDetailsPage";
        public const string EmployeeDetailsRoute = "EmployeeDetailsPage";
    }
}

Of course, the most important elements are the statements and queries. As you can infer, instead of using the ORM capabilities that are integrated in SQLite, you will manually execute queries! However, you can still use the ORM mapper for several operations. For example, you can create and get all items from Departments table with the CreateTable and Table methods because there is no foreign key involved. However, for Employees table, it is better to customize the data management. We will use both ways in our app.

NOTE: In this folder, there is also a FileAccessHelper class which is used to access the database file. I will not include it here, but you can take a look at the GitHub repository for the code.

Step 4. Now let's define the models. Create the following classes and structure:

There are two types: models that work as tables, and models that work as views. A view is a projection, a set of specific fields from a query that involves one or several tables. For example, the EmployeesAndDepartmentQuery query is a view since it includes fields from Employee (all) and Department (DepartmentName) tables. I also created a BaseTable class, which includes an Id property, and all classes inherit from this model. I believe that the SQLite attributes are not necessary since we defined our tables using the statements, but it doesn't hurt to include them (I think). This is the code of the four classes, in order of creation:

using SQLite;

namespace DemoFK.Models
{
    public class BaseTable
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
    }
}

using SQLite;
using DemoFK.Helpers;

namespace DemoFK.Models
{
    [Table(Constants.DepartmentTable)]
    public class Department : BaseTable
    {
        public string Name { get; set; }
    }
}

using SQLite;
using DemoFK.Helpers;
using DemoFK.Models.Views;

namespace DemoFK.Models
{
    [Table(Constants.EmployeeTable)]
    public class Employee : BaseTable
    {
        public string Name { get; set; }
        public int DepartmentId { get; set; }

        public Employee()
        {

        }

        public Employee(EmployeeWithDepartment empDep)
        {
            Id = empDep.Id;
            Name = empDep.Name;
            DepartmentId = empDep.DepartmentId;
        }
    }
}

namespace DemoFK.Models.Views
{
    public class EmployeeWithDepartment : Employee
    {
        public string DepartmentName { get; set; }
    }
}

EmployeeWithDepartment inherits from Employee, meaning that all fields from that table will be included in a query (Id, Name) as well as the DepartmentName field.

Step 5. In Services folder, define an interface: ILocalDatabase. There are 4 methods defined here:

• GetItemsWithQuery returns a collection of T elements using a query string (previously defined in the Constants file).
• GetItems does exactly the same as the previous method, except that this one uses the ORM capabilities of the library (the most well-known use case of SQLite-net)
• ExecuteQuery is used for Insert, Update, and Delete operations
• CountItemsWithQuery for demonstration purposes, it will be used to see how much data is in a table and see if it was created successfully

using DemoFK.Models;

namespace DemoFK.Services
{
    public interface ILocalDatabaseService
    {
        Task<IEnumerable<T>> GetItemsWithQuery<T>(string query) where T : BaseTable, new();
        Task<IEnumerable<T>> GetItems<T>() where T : BaseTable, new();
        Task<bool> ExecuteQuery(string query);
        Task<int> CountItemsWithQuery(string query);
    }
}

Step 6. In the same folder, create the LocalDatabaseService class, which implements the ILocalDatabaseService interface. There's a lot of code here so I will break it down:

First, the namespaces and the class definition:

using SQLite;
using System.Diagnostics;

using DemoFK.Models;
using DemoFK.Helpers;

namespace DemoFK.Services
{
    public class LocalDatabaseService : ILocalDatabaseService
    {

Two private fields are defined, the database file path and the connection to interact with SQLite:

        private string dbPath;
        private SQLiteAsyncConnection connection;

The constructor sets the dbPath field:

        public LocalDatabaseService(string dbPath)
        {
            this.dbPath = dbPath;
        }

The Init method:

• checks if the connection has been set.
• if it hasn't, it creates the SQLiteAsyncConnection instance.
• The Tracer and Trace members from connection allow us to see the SQL commands generated each time we interact with the local database file, for debugging purposes.
• Several methods are called, and they are defined below. The idea is that the tables are created, we count how much data there is in the database; if it is empty, we insert mock data and check if it was successfully created.

        private async Task Init()
        {
            if (connection != null)
                return;

            try
            {
                connection = new SQLiteAsyncConnection(dbPath);

                connection.Tracer = new Action<string>(q => Debug.WriteLine(q));
                connection.Trace = true;

                await CreateTables();
                var count = await CountItems();

                if (count == 0)
                {
                    await AddInitialData();
                    await CheckData();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
        }

Let's see the specific code of the aforementioned methods:

CreateTables: As you can see, the two statements defined in Constants are used here and passed each to ExecuteQuery method (which is defined in the interface).

        private async Task CreateTables()
        {
            var createTableStatements = new List<string>()
            {
                Constants.CreateDepartmentTableStatement,
                Constants.CreateEmployeeTableStatement
            };

            foreach (var statement in createTableStatements)
                await ExecuteQuery(statement);
        }

How is ExecuteQuery method defined? Let's take a look:

        public async Task<bool> ExecuteQuery(string query)
        {
            await Init();

            var op = await connection.ExecuteAsync(query);
            return op > 0;
        }

The above method checks if the connection has been set; then, it calls its ExecuteAsync method and returns the number of records that were affected by the operation.

Now, let's continue with CountItems method, which counts the number of items per table. The CountItemsWithQuery method is invoked here:

        private async Task<int> CountItems()
        {
            var tables = new string[] { Constants.DepartmentTable, Constants.EmployeeTable };
            var count = 0;

            foreach (var table in tables)
            {
                var countQuery = $"SELECT COUNT(*) FROM {table}";
                var tableCount = await CountItemsWithQuery(countQuery);
                Debug.WriteLine($"{table}: {tableCount}");
                count += tableCount;
            }

            return count;
        }

In CountItemsWithQuery method, the ExecuteScalarAsync method is used since our query returns an integer number of rows. This method was also defined in the interface:

        public async Task<int> CountItemsWithQuery(string query)
        {
            await Init();

            return await connection.ExecuteScalarAsync<int>(query);
        }

Next up, the AddInitialData method, which inserts information in each table:

        private async Task AddInitialData()
        {
            var commands = new List<string>()
            {
                "INSERT INTO departments VALUES (1, 'Kitchen'), (2, 'Sales'), (3, 'Accounting');",
                "INSERT INTO employees VALUES (1, 'SpongeBob SquarePants', 1), (2, 'Squidward Tentacles', 2), (3, 'Mr. Krabs', 3), (4, 'Pearl Krabs', 3);",
            };

            foreach (var command in commands)
            {
                var op = await ExecuteQuery(command);
                Debug.WriteLine(op);
            }
        }

We can validate if the data is there with the CheckData method:

        private async Task CheckData()
        {
            var item = await connection.Table<Department>().Where(v => v.Id == 1).FirstOrDefaultAsync();
            Debug.WriteLine(item?.Name);
        }

The final two methods are required as they are defined in the interface and return a list of items, either from a query or by using the Table method:

        public async Task<IEnumerable<T>> GetItems<T>() where T : BaseTable, new()
        {
            await Init();

            return await connection.Table<T>().ToListAsync();
        }

        public async Task<IEnumerable<T>> GetItemsWithQuery<T>(string query) where T : BaseTable, new()
        {
            await Init();

            return await connection.QueryAsync<T>(query);
        }

And that's basically it. The next steps involve setting up and consumption, so I'll focus on the specific, most important code:

In App.xaml.cs, an ILocalDatabaseService public static object is defined to get access to the methods from anywhere in the app. It is injected in the constructor (in MauiProgram, we will register all dependencies).

public partial class App : Application
{
    public static ILocalDatabaseService LocalDb { get; private set; }

    public App(ILocalDatabaseService localDb)
    {
        ...

        LocalDb = localDb;
        ...
    }
}

The GenericListViewModel will be used as the BindingContext of a ContentPage that displays a list of items and supports navigation to another page with the specific details of the selected item (or a new one). The important part here is the GetItemsAsync method, which invokes the GetItemsWithQuery from the database service class and uses a query parameter that defines what we want to see in the page:

    public partial class GenericListViewModel<T> : BaseViewModel where T : BaseTable, new()
    {
        ...

        [RelayCommand]
        private async Task GetItemsAsync()
        {
            if (IsBusy)
                return;

            try
            {
                IsBusy = true;

                var items = await App.LocalDb.GetItemsWithQuery<T>(query);

                // if you want to get all items from specific table (no join involved) you can also use
                // items = await App.LocalDb.GetItems<T>();

                if (Items.Count != 0)
                    Items.Clear();

                foreach (var item in items)
                    Items.Add(item);
            }
            catch (Exception ex)
            {
                await Shell.Current.DisplayAlert("Error!", ex.Message, "OK");
            }
            finally
            {
                IsBusy = false;
            }
        }
    }
}

For example, let's say we have two pages: one for departments (DepartmentListView) and another one for employees (EmployeeListView); in the first one we want to display the list of departments and in the second one, we show a list of Employees that also includes the department they belong to. We know we already have a query (EmployeesAndDepartmentQuery, which defines a JOIN between the two tables). We specify the argument in MauiProgram because we register the ViewModel as a dependency; since the constructor defines arguments, we use ActivatorUtilities.CreateInstance:

...
        builder.Services.AddSingleton<ILocalDatabaseService>(
            s => ActivatorUtilities.CreateInstance<LocalDatabaseService>(s, dbPath));

        builder.Services.AddTransient(
            s => ActivatorUtilities.CreateInstance<GenericListViewModel<Department>>(
                s, Constants.AllDepartmentsQuery, Constants.DepartmentDetailsRoute));

        builder.Services.AddTransient(
            s => ActivatorUtilities.CreateInstance<GenericListViewModel<EmployeeWithDepartment>>(
                s, Constants.EmployeesAndDepartmentQuery, Constants.EmployeeDetailsRoute));
...

There you have it! Each View will require a ViewModel, which is the same (GenericListViewModel) with different generic type and arguments. Thanks to ActivatorUtilities, we can customize this definition by including the following arguments: a query and a route. The query is used by GetItemsAsync in the ViewModel, while the route is used for navigation.

For the rest of the code, please check the GitHub repository. In this post I'm not including the Views definition (because it is pretty obvious, just bindings) or the DetailsView/DetailsViewModel that I use to add, update, or delete information from the tables. It shouldn't be complicated to understand, but if you do have a question, write a comment and I'll do my best to help :-)

Reminder: Source code available here

Ok, so let's run the app. We can see some information in the Output window (in Visual Studio) as well as in the phone:

First, creating the tables:

Then, counting data, inserting values and retrieving the first department (Kitchen). Finally, get departments data:

And we DO have data in our app!

What do we get when we click on the menu and access the Employees item? First, this query is executed:

And the app shows the information from both tables.

Well, this was a long post but hopefully, useful for you. You can check the rest of the code to see how to insert, update, and delete. In this case, I also used statements instead of the ORM capabilities (you can use both without any issues).

Remember to follow the rest of the interesting publications of the .NET MAUI Advent Calendar 2022. You can also follow the conversation on Twitter with the hashtag #dotNETMAUIAdvent.

Thanks for reading. Until next time!

Luis