SQLite-Net Extensions – one-to-many relationships

In the 3rd post from SQLite-Net Extensions series we are covering the last type of relationship – one-to-many (and the opposite – many-to-one).

One-to-many, many-to-one

One-to-many relationships are used in general for parent-children or whole-elements relations. Classic examples are: bus and passengers, document and elements etc.

One-to-many relationship means that the one-end entity knows about its children and many-end entity has a reference (foreign key) to its parent (but not necessarily knows about it).

On the other hand, the opposite relationship to one-to-many is many-to-one. In that case, many-end entity has a reference to its parent and knows about it, but the one-end entity doesn’t necessarily know about its children (at least not directly).

I used a verb to know several times – so it’s time to explain it 🙂 By “knowing” about the other end of a relationship I understand having a reference to it. It means that, for instance, in many-to-one relationship, the one-end of it doesn’t have a reference to its children.

However in most cases what we’d like to have is a hybrid of one-to-many and many-to-one relationships. I will call it one-to-many with inversion. We want both parent to know about its children, and each children to know about its parent.

In this post we’ll cover one-to-many with no reversion and one-to-many with reversion, as it also includes many-to-one relationship so you can have a comprehensive overview. We are going to see it on the example of Employee and Duty* entities. Each employee has a list of his duties, whilst each single duty is assigned to only one employee.

* “Task” would probably be a better name, but I didn’t want to make it confused with .NET Tasks 🙂

One-to-many with no inversion

Firstly, let’s model this kind of relationship:

One-to-many with no inversion – class diagram

Now we can transform it into C# classes:


[Table("Employees")]
public class Employee
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string Name { get; set; }
public string LastName { get; set; }
[OneToMany]
public List<Duty> Duties { get; set; }
}

view raw

Employee.cs

hosted with ❤ by GitHub

In the Employee class (parent, one-end of the relationship) we define the collection of children, decorated with OneToManyAttribute. Collections types supported as for the time of writing this post by SQLite-Net Extensions are List and Array and can be used as you prefer.

Let’s now see how the child entity (Duty) looks like:


[Table("Duties")]
public class Duty
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string Description { get; set; }
public DateTime Deadline { get; set; }
[ForeignKey(typeof(Employee))]
public int EmployeeId { get; set; }
}

view raw

Duty.cs

hosted with ❤ by GitHub

In the Duty class (child, many-end of the relationship) we need to have a foreign key to parent entity defined. For that purpose, we create a property representing it (EmployeeId), decorating it with ForeignKeyAttribute, additionally specifying the type of parent referenced (Employee).

That’s it. We can already use it in our code:


var db = new SQLiteConnection(new SQLitePlatformAndroid(), Constants.DbFilePath);
db.CreateTable<Employee>();
db.CreateTable<Duty>();
var employee = new Employee
{
Name = "Andrew",
LastName = "Programmer"
};
var duty1 = new Duty()
{
Description = "Project A Management",
Deadline = new DateTime(2017, 10, 31)
};
var duty2 = new Duty()
{
Description = "Reporting work time",
Deadline = new DateTime(2022, 12, 31)
};
db.Insert(employee);
db.Insert(duty1);
db.Insert(duty2);
employee.Duties = new List<Duty> {duty1, duty2};
db.UpdateWithChildren(employee);
var employeeStored = db.GetWithChildren<Employee>(employee.Id);

No rocket-science here. What’s interesting for us it how employeeStored looks in the end:

One-to-many with no inversion

As you can see, GetWithChildren method returned object of type Employee with its Duties collection properly retrieved (containing two duties assigned to the employee before). Moreover, each children has its foreign key (EmployeeId) automatically retrieved from the DB – there is no overhead here, this is simply foreign key field stored in the same SQLite database table (Duties).

One-to-many with inversion (one-to-many + many-to-one)

As previously, let’s first see how the class diagram changes after adding inversion:

One-to-many with inversion – class diagram

What changed is that now each Duty has a property of type Employee.

To realize the above class diagram and make each children (each Duty in our case) knowing about its parent (having a reference to responsible Employee), the only thing we need to do is to add the following property to Duty model class:

[ManyToOne]
public Employee Employee { get; set; }

so the model class finally looks as follows:


[Table("Duties")]
public class Duty
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string Description { get; set; }
public DateTime Deadline { get; set; }
[ForeignKey(typeof(Employee))]
public int EmployeeId { get; set; }
[ManyToOne]
public Employee Employee { get; set; }
}

As you can see, we have just created many-to-one relationship by using ManyToOneAttribute. So currently we have the hybrid of both types of relationships within out two models.

The usage in our code doesn’t need to be changed at all. Now, employeeStored entity after being initialized by the same GetWithChildren method as previously, for each Duty in addition to EmployeeId field also contains Employee property properly retrieved by SQLite-Net Extensions:

One-to-many with inversion

Again, there is no db overhead here, because when retrieving Employee entity from the database we already have it, so the operation of initializing the Employee entity in each Duty contained within Duties collection doesn’t require any more database querying.

Summary

Today we’ve seen how to model and use one-to-many (with and without inversion) relationships in SQLite database with the usage of SQLite-Net Extensions. The automatic initialization of one-end or many-end entities by the ORM is extremely helpful when dealing with such kind of objects in our app. The amount of code to be written is also minimal.

We’ve covered one-to-many and a hybrid of one-to-many + many-to-one relationships, but of course you can also define just many-to-one relationship – the models would then look as in this Gist. For me, it makes very limited sense, but maybe can be useful in some specific situations.

I invite you to read my other posts about SQLite-Net Extensions ORM, where we also covered the other types of relationships that can be modeled using this library.

As this is already the third post from this series, I’d like to ask you: what do you think about SQLite-Net Extensions ORM? Do you find it useful? Do you use it in your apps? Can you can recommend some other libraries/ORMs to use with SQLite database that allow to model different kind of relationships?

.NET full stack web developer & digital nomad
0 0 votes
Article Rating
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
D Mukherjee
D Mukherjee
6 months ago

Thank you for this article. But, can you please give an example to work with InsertWithChildren ?

Dawid
Dawid
6 months ago
Reply to  D Mukherjee

Hi, unfortunately, I’m not planning any updates here soon. I am sure you’ll find all info in the docs or on StackOverflow 🙂