Clean up intermediate records of SQLite many-to-many relationships

The short story:

I recently received a bug ticket stating that records in a many-to-many relationship table in our SQLite database are not deleted when deleting one of the base objects. My first assumption was that this problem occurs due to an incorrectly configured OR mapper – unfortunately, it turned out that SQLite-Net Extensions doesn’t support this scenario at all.

The long story:

Let’s take a look at the following classic example of a many-to-many relationship, built with the SQLite-Net Extensions OR Mapper on top of a local SQLite database – given the base objects Student and Course, each student may attend several courses, while most courses will have multiple students assigned to them. Due to this relationship, we need an extra table that only contains student-to-course relationships:

public class Student
{
	[PrimaryKey]
	public int Id { get; set; }

	[ManyToMany(typeof(StudentIncident))]
	public List<Course> Courses { get; set; }
}

public class Course
{
	[PrimaryKey]
	public int Id { get; set; }

	[ManyToMany(typeof(StudentIncident))]
	public List<Student> Students { get; set; }
}

public class StudentsToCourses
{
	[PrimaryKey]
	public int Id { get; set; }

	[ForeignKey(typeof(Student))]
	public int StudentId { get; set; }

	[ForeignKey(typeof(Course))]
	public int CourseId { get; set; }
}

When calling the connection.InsertWithChildren(student, recursive: true) method with a Student object that has a Course assigned, one line is correctly inserted in all three tables.

The problem is: When deleting the Student we just inserted into the database using the connection.Delete(student, recursive: true) method, the line is removed from the table of students, but the intermediate record in the StudentsToCourses table still exists, now pointing to a Student that does not exist any more…

According to the SQLite-Net Extensions project team, this behavior is not planned to be changed, because intermediate records will not be accessed directly, so they are harmless and need not be cleaned up.

This is true for the example presented in the issue linked above, but not for our code sample: When not using the [AutoIncrement] attribute on [PrimaryKey] columns (and the official SQLite documentation recommends to not use it in general due to extra CPU, memory, disk space, and disk I/O overhead), IDs within the Student table might be re-used, leading to an unwanted student-to-course relationship.

The solution(s):

Obviously, one solution is to use only AutoIncrement primary keys and live with the facts that Insert operations will be slightly less performant, and that the intermediate table will grow and become less readable.

An alternative approach is to remove relationships manually before deleting base objects. For our code sample, this would mean setting the student object’s Courses property to NULL before deleting it. Don’t forget to call Update inbetween, otherwise the change won’t be passed on to the database!

student.Courses = null;
connection.UpdateWithChildren(student);
connection.Delete(student, recursive: true);

Of course, this also comes with a performance penalty, as deleting entities will be slower (due to the extra database operation that needs to be carried out before the actual deletion). If you can live with this, there is a third alternative that has the additional advantage that no manual relationship update is necessary:

We simply create an extension method that does all the clean up automatically before deleting the base object! It could be named DeleteWithManyToManyRecords and expects an instance of type SQLite.Net.SQLiteConnection as first parameter:

public static void DeleteWithManyToManyRecords(this SQLiteConnection connection, object entity)
{
	// TODO
}

What does this method need to do? Let’s step through it:

  • Loop through all properties that are flagged with the [ManyToMany] attribute,
  • set these properties’ values to NULL,
  • invoke the Update method on the base object, and finally
  • delete it!

With this structure in mind and a little bit of .NET reflection knowledge, the code should be self-explanatory:

public static void DeleteWithManyToManyRecords(this SQLiteConnection connection, object entity)
{
	foreach (var prop in entity.GetType().GetTypeInfo().DeclaredProperties)
	{
		var manyToMany = prop.GetCustomAttribute<SQLiteNetExtensions.Attributes.ManyToManyAttribute>();
		if (manyToMany != null)
		{
			prop.SetValue(entity, null);
		}
	}
	await connection.UpdateWithChildren(entity);
	await connection.Delete(entity, recursive: true);
}