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:

Now we can transform it into C# classes:

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:

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:

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

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:

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; }



  }






        view raw

        

          Employee_withInv.cs

        

        hosted with ❤ by GitHub
      


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:

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?

Related Posts


SQLite-Net Extensions – one-to-one relationships
In this second short post from SQLite-Net Extensions series, we're going to see how to…


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…


[.NET Internals 02] Stack and heap -  .NET data structures
In the second post of .NET Internals series, we're going to investigate the organization of…