Many To Many Relationships not updating in Sqlite Extensions

Question

Please pardon the wall of code, but I need to set the stage....

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

    [ManyToMany(typeof(StudentStaff))]        
    public List<Staff> Teachers { get; set; }

    [ManyToMany(typeof(StudentGuardian))]
    public List<Guardian> Guardians { get; set; }
 }

  public class Guardian
     {
         [PrimaryKey]
         public string Id { get; set; }
         public string Name{get;set;}       

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

public class Staff
{
    [PrimaryKey]
    public string Id { get; set; }
    public string Name { get; set; }

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

public class StudentStaff
{
    [ForeignKey(typeof(Student))]
    public string StudentId { get; set; }
    [ForeignKey(typeof(Staff))]
    public string StaffId { get; set; }
}

 public class StudentGuardian
    {
        [ForeignKey(typeof(Student))]
        public string StudentId { get; set; }
        [ForeignKey(typeof(Guardian))]
        public string GuardianId { get; set; }
}

Given those classes how should I insert the Students, Staff and Guardians into the database while maintaining the relationships? I should note that the server returns populated Student records, so that is my starting point.

I tried conn.InsertOrReplaceWithChidlren(student);...that inserted the student, the studentstaff and the studentguardian records but not the actual staff or guardian. I tried conn.InsertOrReplaceWithChildren(student); conn.InsertOrReplaceWithChildren(student.Teachers); conn.InsertOrReplaceWithChildren(student.Guardians); Oddly that ended up with staff, guardian and teachers inserted but neither of the intersection tables had any data...

--Update--- I just tried conn.InsertOrReplaceWithChildren(student.Teachers); conn.InsertOrReplaceWithChildren(student.Guardians); conn.InsertOrReplaceWithChildren(student); And it worked perfectly...the question is why? Why does a many to many seem to be dependent on operation order?

Answer 1

SQLite-Net Extensions requires that all the objects have been assigned an unique ID to establish the relationships. With AutoIncrement primary keys this means that all objects have been already inserted in the database.

The order may also affect if the inverse relationships have not been set, it's usually a good idea to set the inverse relationship or set those attributes as ReadOnly to avoid side effects.

That being said, SQLite-Net Extensions provides utility methods for helping with this task. In this case, you could make use of cascade operations to perform a single insert. To enable cascade operations, define the CascadeOperations property of the relationship attribute:

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

    [ManyToMany(typeof(StudentStaff), CascadeOperations = CascadeOperation.All)]        
    public List<Staff> Teachers { get; set; }

    [ManyToMany(typeof(StudentGuardian), CascadeOperations = CascadeOperation.All))]
    public List<Guardian> Guardians { get; set; }
 }

  public class Guardian
  {
      [PrimaryKey]
      public string Id { get; set; }
      public string Name{get;set;}       

      [ManyToMany(typeof(StudentGuardian), CascadeOperations = CascadeOperation.All, ReadOnly = true))]
      public List<Student> Students { get; set; }
  }

public class Staff
{
    [PrimaryKey]
    public string Id { get; set; }
    public string Name { get; set; }

    [ManyToMany(typeof(StudentStaff), CascadeOperations = CascadeOperation.All, ReadOnly = true))]
    public List<Student> Students { get; set; }
}

In this sample I also included ReadOnly property to make Guardian and Staff relationships to Student a read-only property so SQLite-Net Extensions will ignore this relationship when saving the objects to the database.

Then, you can use the recursive parameter of any SQLite-Net Extensions method to true:

conn.InsertOrReplaceWithChildren(student, recursive: true);

SQLite-Net Extensions will handle inverse relationships and relationship loops correctly.