SQLite-Net Extensions – one-to-one relationships

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:


[Table("RegistrationCertificates")]
public class RegistrationCertificate
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string RegistrationNumber { get; set; }
public string VIN { get; set; }
public string OwnerData { get; set; }
}


[Table("Vehicles")]
public class Vehicle
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string Brand { get; set; }
public DateTime ProductionDate { get; set; }
public decimal EngineCapacity { get; set; }
[ForeignKey(typeof(RegistrationCertificate))]
public int RegistrationCertificateId { get; set; }
[OneToOne]
public RegistrationCertificate RegistrationCertificate { get; set; }
}

view raw

Vehicle.cs

hosted with ❤ by GitHub

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:


var db = new SQLiteConnection(new SQLitePlatformAndroid(), Constants.DbFilePath);
db.CreateTable<Vehicle>();
db.CreateTable<RegistrationCertificate>();
var vehicle = new Vehicle
{
Brand = "Renault",
EngineCapacity = 1.9m,
ProductionDate = new DateTime(2001, 01, 01)
};
var certificate = new RegistrationCertificate
{
RegistrationNumber = "AB 12345",
OwnerData = "Dawid Sibiński",
VIN = "1312BS1312ASDSSVVW"
};
db.Insert(vehicle);
db.Insert(certificate);
vehicle.RegistrationCertificate = certificate;
db.UpdateWithChildren(vehicle);
var vehicleStored = db.GetWithChildren<Vehicle>(vehicle.Id);

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:


[Table("RegistrationCertificates")]
public class RegistrationCertificate
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public string RegistrationNumber { get; set; }
public string VIN { get; set; }
public string OwnerData { get; set; }
// added to have Inversion relationship
[ForeignKey(typeof(Vehicle))]
public int VehicleId { get; set; }
[OneToOne]
public Vehicle Vehicle { get; set; }
}

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&lt;RegistrationCertificate&gt;(certificate.Id);

and notice that certificateStored already contains its associated Vehicle object:

One-To-One – Vehicle initialized

Summary

In today’s short post we’ve seen how to create one-to-one relationship between two entities in SQLite database, using SQLite-Net Extensions ORM. It’s very easy and doesn’t require using Entity Framework 🙂 or writing SQL queries directly in our code.

We’ve covered two types of one-to-one relationships: one-way and both-ways. The choice between those two depends on the usage and purpose of the relationship we model.

I hope you’ll find it useful one day 😉

.NET full stack web developer & digital nomad
5 1 vote
Article Rating
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jose David Alvarez Chavarria
Jose David Alvarez Chavarria
11 months ago

Gracias por esto, soy nuevo programando en sql-lite y este tipo de tutoriales son los que me motivan a seguir aprendiendo.