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:
.gist table { margin-bottom: 0; }
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:
.gist table { margin-bottom: 0; }
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 (), decorating it with , additionally specifying the type of parent referenced (Employee).
That’s it. We can already use it in our code:
.gist table { margin-bottom: 0; }
No rocket-science here. What’s interesting for us it how looks in the end:
One-to-many with no inversion
As you can see, method returned object of type Employee with its collection properly retrieved (containing two duties assigned to the employee before). Moreover, each children has its foreign key () automatically retrieved from the DB – there is no overhead here, this is simply foreign key field stored in the same SQLite database table (Duties).
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; }read more...
In this second short post from SQLite-Net Extensions series, we’re going to see how to create one-to-one relationships using this tiny ORM.
One-To-One
This is the simplest type of database relationship. An example could be vehicle and registration certificate – each vehicle has one and only one registration certificate, and one registration certificate is associated with one and only one vehicle (excluding some extraordinary law rules in other countries that I don’t know 🙂 ).
We can model it in two ways:
as a one-way relationship – in this case only one of the relationship’s ends knows about the other one
as a both-ways (with inversion) relationship – both relationship’s ends know about each other.
One-To-One with no inversion (one-way)
This kind of relationship looks as follows:
One-To-One – one-way – class diagram
We use it when we assume it’s enough that Vehicle knows about RegistrationCertificate, but the document doesn’t necessarily need to know which car/motor it’s associated with (at least directly).
In the code, we then create two model classes using SQLite-Net Extensions:
.gist table { margin-bottom: 0; }
.gist table { margin-bottom: 0; }
What’s interesting here is the ForeignKeyAttribute defined on RegistrationCertificateId property. It is – as its name says – the foreign key to the primary key of related entity (of type RegistrationCertificate).
The related entity property itself is decorated with OneToOneAttribute.
There’s nothing more we need to do to have this relationship modeled. We can already use it:
.gist table { margin-bottom: 0; }
Nothing special here, right? It looks very similar to what we’ve seen in the previous post about many-to-many relationships. What’s interesting for us it that in the end, when Vehicle object is retrieved from the database using GetWithChildren method, its RegistrationCertificate property is filled as well:
One-To-One – filled related entity
One-To-One with inversion (both-ways)
This kind of relationship models itself as below:
One-To-One – with inversion – class diagram
What actually changed on the diagram compared to the relationship with no inversion is that now RegistrationCertificate has a property of type Vehicle (in the code – a reference to Vehicle related object and a foreign key).
In our case, this is even more “real” – it would be nice if a Vehicle knows about its RegistrationCertificate, but also when we look at the certificate we’d like to see to which car it belongs.
We can now model it in the code. The only thing that changes in the model classes is addition of a reference and foreign key to Vehicle in RegistrationCertificate model, so I’m presenting only this class’s updated code:
.gist table { margin-bottom: 0; }
What’s cool here is that we don’t need to modify our code for saving entities with relationships into SQLite database. We can simply add the following line at the end:
var certificateStored = db.GetWithChildren<RegistrationCertificate>(certificate.Id);read more...
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 relationsin 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:
