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

Question

I have a similar situation, to this post the delete works but only on students and classes table, in the student_classes table the elements are not deleted.

  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
{
   [PrimaryKey, AutoIncrement]
    public int StudentClassesId { get; set; }

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

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

To delete I use the following code

conn.Delete(student, true);

I have no problem during insert and updates thanks

p.s. My classes have different names, but doesn't matter, the code is really identical

score 1 · Answer 1 · answered Feb 04 '16 at 12:44

1

You do not have a primary key in your Students_Classes class. That is the problem.

answered Feb 04 '16 at 12:44

Julia

174
12

damn sorry, of course I have, I followed the comments in the post I linked, I just copied and pasted from the link and not from my code :/ Thanks ! – PandaSharp Feb 04 '16 at 12:48

score 0 · Accepted Answer · answered Feb 05 '16 at 10:04

Cascade deletion doesn't remove intermediate records. These records are harmless, but completely useless, you can remove them manually. For example:

// Fetch IDs of intermediate records to delete
var deleteIds = conn.Table<Student_Classes>()
    .Where(e => e.StudentFId == student.StudentId)
    .Select(e => e.StudentFId).ToList();

// Perform batch deletion
conn.DeleteAllIds<Student_Classes>(deleteIds);

Or more performant (but less refactor friendly):

var deleteQuery = "DELETE FROM Student_Classes WHERE StudentFId == ?";
conn.Execute(deleteQuery, student.StudentId);

thanks! this was what I was worried about, meanwhile I already used this "workaround" — PandaSharp, Feb 05 '16 at 10:19

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

2 Answers2