In today’s post we’re going to see what is SQLite-Net Extensions ORM and how to use it to create many-to-many relationship in SQLite database. The other types of relationships will be described in separate posts.
What is SQLite-Net Extensions ?
As you develop any mobile app, sooner than later you need to keep your app’s data in some persistent storage. In my MoneyBack project I’ve chosen SQLite database using SQLite.NET library for performing operations on it. This is actually very simple and easy-to-use database framework, but I recently realized I need to model some relations in my database. SQLite.NET doesn’t offer any nice utilities to model such relations.
However, if you need to model any relations in your SQLite database, there is a wrapper on SQLite.NET which allows to do that – it is SQLite-Net Extensions. It basically extends the core functionalities of SQLite.NET by adding elements that allow to easily handle relationships, including one-to-one, one-to-many, many-to-one and many-to-many.
In this post we are going to see how to create many-to-many relationship using this library. I needed this kind of relationship to model the connection between Person and Event entities in my app.
Let’s see many-to-many relationship on an example of two entities: Person and Event. An event (entity of type Event) may contain zero or more participants (entities of type Person) whilst a person may be assigned to zero or more events. This is a typical many-to-many relationship which we are going to set up in our database now.
Install SQLite-Net Extensions
If you’ve previously been using SQLite.NET in your project – uninstall it first. I didn’t do it before starting to use SQLite-Net Extensions and I have many troubles with Visual Studio resolving my references incorrectly. SQLite-Net Extensions is a wrapper for SQLite.NET, so it already contains this library and additionally extends it by adding some extra functionalities for handling relationships.
SQLite-Net Extensions can be installed as a Nuget package into your solution. According to the version you’d like to use, execute appropriate command in Package Manager Console in Visual Studio:
Install-Package SQLiteNetExtensions -Version 1.3.0
Install-Package SQLiteNetExtensions.Async -Version 1.3.0
Define model classes
Next we need to define our Person and Event model classes and establish the relationships between them. Below, you can find the code of both classes:
As you can see, the models look almost the same as SQLite.NET db entities, with the following exceptions:
- ManyToManyAttribute – on both entities you can notice this attribute defined. On Person model class I decorate Events collection with it whereas on Event model class I decorate Participants collection with it. Simple as that.
- PersonEvent – you may have noticed that as an argument to ManyToManyAttribute on both models I passed PersonEvent type. As you may know, in modelling many-to-many relationships we need an intermediate entity in order to store such kind of relationship in the database tables. The classic example is Student-Course relationship:
We also need to define such intermediate entity in our code.
The implementation of PersonEvent intermediate model class looks as follows:
Thanks to PrimaryKey attributes defined on Person and Event entities the ORM will be able to determine to which primary keys the foreign keys in this intermediate table relate.
In the ManyToManyAttribute, except the type of intermediate entity, you can set CascadeOperations, which specifies how the cascading should be handled when working with the entities (e.g. if cascade delete operation should be performed when one of the relationship’s sides is removed).
Inserting and reading data
As soon as our model classes are defined, we can write and read the data with many-to-many relationships. The following code presents a simple way to create a new person and assign it to an event:
Lines 1-4 contain the database initialization (Constants.DbFilePath just returns an Android path of SQLite database file) and creation of all 3 tables in the database.
Lines 6-18 are simply the creation of Person and Event objects, filled with most basic details.
In lines 21 and 22 we firstly save our person1 and event1 entities separately, because in order to establish the relationships we need the primary keys of those entities, that are assigned by the database while saving. This can be also simplified by using recursive operations – more details in the official documentation of the ORM.
After that, we assign just created person to the event (line 24) and then the most rocket-science part comes:
This method does the write magic – it updates the person with all its children – so in our case, the Events collection. It will make the relationship established.
In order to prove it, in lines 27 and 28 we can check if the relationship collections are populated with children in both entities by calling GetWithChildren extension methods:
That’s how SQLite-Net Extensions ORM works. It doesn’t provide any lazy-loading of related entities – it just adds/retrieves to/from the database exactly what you tell it to. The limitation here is that if you access Person.Events collection you can see the events this person is in relation with, but if you access Person.Events you will not see all people registered for this event.
SQLite-Net Extensions is an ORM, which is a wrapper for classic SQLite.NET library. It adds the extension methods/attributes to handle relationships in SQLite database. It doesn’t provide any lazy-loading mechanism, instead exposing methods for getting/saving entities along with their children (related entities). It’s lightweight and rather easy to implement, so for small solutions like mobile apps I totally prefer it to writing and maintaining SQL queries directly in C# in order to handle relationships.
In the next posts from the series about SQLite-Net Extensions ORM, I will present to you the other types of relationships this ORM offers. Stay tuned 🙂