How to delete a relationship from many to many table in sqlite-net extension?

Question

I've a many to many relationship between students and classes using SQLite-net Extensions:

public class Students
{
    [PrimaryKey, AutoIncrement]
    public int StudentId { get; set; }
    public string Name { get; set; }

    [ManyToMany(typeof(Students_Classes))]
    public List<Classes> Classes { get; set; }
}

public class Classes
{
    [PrimaryKey, AutoIncrement]
    public int ClassId { get; set; }
    public string Name { get; set; }

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

public class Students_Classes
{
    [ForeignKey(typeof(Students))]
    public int StudentFId { get; set; }

    [ForeignKey(typeof(Classes))]
    public int ClassFId { get; set; }
}

I add a relationship this way:

dbConnection.InsertOrReplace(new Students_Classes { StudentFId = sId, ClassFId = cId });

But when I want to delete a relationship:

var validRelation = dbConnection.Find<Students_Classes>(x => x.StudentFId = sId && x.ClassFId = cId);
if (validRelation == null)
    return;

dbConnection.Delete(validRelation);

I get an error says cannot delete because it has no PK. I can get a Student with all his classes, remove one class then save it again with his classes but there can be performance issues.

How to remove a relationship in a many to many relationship? thanks.

Answer 1

Pretty easy, just add a primary key to your relationship class:

public class Students_Classes
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [ForeignKey(typeof(Students))]
    public int StudentFId { get; set; }

    [ForeignKey(typeof(Classes))]
    public int ClassFId { get; set; }
}

Anyway using SQLite-Net Extensions you don't need to manage the relationship table by yourself, just add or remove the children item from the list and call UpdateWithChildren on the object.

Take a look at the Integration Tests project to see how it works.