Foreign keys in local databases with SQLite-net and .NET MAUI
Tags: sqlite, sqlitenet, dotnetmaui, maui
Published: 2022-12-05 12:40:20

_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](https://dev.to/icebeam7/net-maui-advent-calendar-48d0)** for more interesting .NET MAUI articles posted by community members._

A common misconception about [SQLite-net](https://github.com/praeclarum/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](https://github.com/icebeam7/DemoFK).


**Step 1.** Create the following project structure:

![Folder structure of the project](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a3v6wq5lokbfr1lxeouw.png)

**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)


![Nuget Packages included in the project](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qhfrrqxyxm2rrq3xazo0.png)

**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:


![Model definitions](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/et2fpsa41da4hj55dyzt.png)

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](https://github.com/icebeam7/DemoFK)

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:
![Creating tables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s1v4yqibtipnar5rs6dj.png)

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

![Data insertion and validation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gpzmaof84m0ts2izcbz0.png)

And we DO have data in our app!

![Department List](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/77c24g9zvxxj8syjflbo.png)

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


![Get Employees and their Department](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zadbczyb6sjo3h1i4hjt.png)

And the app shows the information from both tables.


![Employees and Departments](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dr1eo5m2amm0wpl1qjgj.png)

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](https://dev.to/icebeam7/net-maui-advent-calendar-48d0). You can also follow the conversation on Twitter with the hashtag #dotNETMAUIAdvent.

Thanks for reading. Until next time!

Luis