SQLite-Net Extensions - many-to-many relationships

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.

Table of Contents

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.

Many-to-many relationship

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:

synchronous:

Install-Package SQLiteNetExtensions -Version 1.3.0

asynchronous:

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:

	// Person class modelling People table
	[Table("People")]
	public class Person
	{
	[PrimaryKey, AutoIncrement]
	public int Id { get; set; }

	public string Name { get; set; }

	public string LastName { get; set; }

	public string PhoneNumber { get; set; }

	public string Email { get; set; }

	[ManyToMany(typeof(PersonEvent))]
	public List<Event> Events { get; set; }
	}


	// Event class modelling Events table
	[Table("Events")]
	public class Event
	{
	[PrimaryKey, AutoIncrement]
	public int Id { get; set; }

	public string Name { get; set; }
	public DateTime Date { get; set; }
	public string Place { get; set; }

	[ManyToMany(typeof(PersonEvent))]
	public List<Person> Participants { get; set; }
	}

view raw

Person_Event.cs

hosted with ❤ by GitHub

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:

Source: smehrozalam

We also need to define such intermediate entity in our code.

The implementation of PersonEvent intermediate model class looks as follows:

	public class PersonEvent
	{
	[ForeignKey(typeof(Person))]
	public int PersonId { get; set; }

	[ForeignKey(typeof(Event))]
	public int EventId { get; set; }
	}

view raw

PersonEvent.cs

hosted with ❤ by GitHub

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:

	var db = new SQLiteConnection(new SQLitePlatformAndroid(), Constants.DbFilePath);
	db.CreateTable<Person>();
	db.CreateTable<Event>();
	db.CreateTable<PersonEvent>();

	var event1 = new Event
	{
	Name = "Volleyball",
	Date = new DateTime(2017, 06, 18),
	Place = "Sports hall"
	};

	var person1 = new Person
	{
	Name = "A",
	LastName = "B",
	PhoneNumber = "123456789"
	};


	db.Insert(person1);
	db.Insert(event1);

	person1.Events = new List<Event> { event1 };
	db.UpdateWithChildren(person1);

	var personStored = db.GetWithChildren<Person>(person1.Id);
	var eventStored = db.GetWithChildren<Event>(event1.Id);

view raw

SavingPersonEvent.cs

hosted with ❤ by GitHub

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:

db.UpdateWithChildren(person1);

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[0] you will not see all people registered for this event.

Summary

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 🙂

5 2 votes

Article Rating

8 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Moustafa khalil

6 years ago

That is so helpful, but I have a question:
If I have an Event 01 that will be shared with multiple persons (A,B). so in Short, Person A, and B will do the same Event.
do I have to clone the event 01 and assign to both of them:
personA.Events = new List { event01 };
personB.Events = new List { event01_Cloned };

not sure if the above concept is correct, due to the maintainability. because later on if I wish to change a property in on of the Events, then i have to do twice or trice… May you you please advise how to solve such scenario?

Author

Dawid Sibiński

Reply to Moustafa khalil

Hello Moustafa,
I don’t get why would you need to “clone” the event objects. You simply create your persons, create the event and assign the same event to each person, as presented in the following snippet an a unit test: https://gist.github.com/iks111/1b523d5c1e6f3d1299098738b6b95e0a

Regards,
Dawid

Nikolai MItov

Hello and thank you for taking the time to create one of the best things I’ve read on the subject. What I do not understand is how do I use UpdateWithChildren or GetWithChildren asynchonously. Do I just wrap them in a task or are there async versions of these methods, because I can not seem to be able to find any.
Thank you in advance if you manage to provide an answer to my little riddle.

Reply to Nikolai MItov

Hello Nikolai,
you can find more details about asynchronous operations in SQLite here: https://bitbucket.org/twincoders/sqlite-net-extensions (see section “Asynchronous operations”). There’s a separate NuGet package to install to have these methods available: https://www.nuget.org/packages/SQLiteNetExtensions.Async/

I personally had some issues with that, that’s why I removed async support from my pet project (https://github.com/iks111/MoneyBack/commit/89fa282aaf9da09a5192766a7f42a4799bd90915)
I also recommend you to read some best practices on accessing the SQLite database: https://stackoverflow.com/questions/2493331/what-are-the-best-practices-for-sqlite-on-android/3689883#3689883

I hope it’s useful.
Dawid

Search Site

SQLite-Net Extensions – many-to-many relationships

What is SQLite-Net Extensions ?

Many-to-many relationship

Install SQLite-Net Extensions

Define model classes

Inserting and reading data

Summary

What is SQLite-Net Extensions ?

Many-to-many relationship

Install SQLite-Net Extensions

Define model classes

Inserting and reading data

Summary

Related Posts

Share This

Share if you liked it:

You may also like

What is Mutation Testing and Why Should You Use It?

5 Reasons To Not Use C# IDE For TypeScript Development

React State Management Basics