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:

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

view raw

RegistrationCertificate.cs

hosted with ❤ by GitHub

	[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);

view raw

OneToOne_noInv.cs

hosted with ❤ by GitHub

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

view raw

RegistrationCertificate_withInv.cs

hosted with ❤ by GitHub

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:

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 😉

5 1 vote

Article Rating

2 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Jose David Alvarez Chavarria

1 year ago

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

Author

Dawid Sibiński

Reply to Jose David Alvarez Chavarria

De nada! 😉

One-To-One

One-To-One with no inversion (one-way)

One-To-One with inversion (both-ways)

Summary

Related Posts

Share This

Share if you liked it:

You may also like

Real .NET Interview Questions (2024/2025)

What is Mutation Testing and Why Should You Use It?

5 Reasons To Not Use C# IDE For TypeScript Development