Many-to-Many does not work as expected

[Juergen]

I have a many-to-many relation between a parent- and a child-class:

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

    [ManyToMany(typeof(OnParent), CascadeOperations = CascadeOperation.All)]
    public List<Child> Children { get; set; }
}

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

    [ManyToMany(typeof(OnParent)]
    public List<Parent> Parents { get; set; }
}

public class OnParent
{
    [ForeignKey(typeof(Parent))]
    public int ParentId { get; set; }

    [ForeignKey(typeof(Child))]
    public int ChildId { get; set; }
}

And I have parents that own the same childs:

Parent p1 = new Parent { Id = 1 };
Parent p2 = new Parent { Id = 2 };

Child c1 = new Child { Id = 1 };
Child c2 = new Child { Id = 2 };

p1.Children.Add(c1);
p1.Children.Add(c2);

p2.Children.Add(c1);
p2.Children.Add(c2);

conn.InsertOrReplaceWithChildren(p1, recursive: true);
conn.InsertOrReplaceWithChildren(p2, recursive: true);

After saving p1 everything is still fine. After saving p2, the children of p1 have disappeared. There are no more entries in the intermediate table "OnParent" for the parent p1.

[Guillermo Gutiérrez]

Recursive operations may have inconsisten behaviours if there are integral reference issues. In this scenario you are setting the p1 to c1 relationship by setting p1.Children, but leaving c1.Parents empty, therefore when saving c1 relationships, all their parents relationships will be removed.

To avoid this issue you can:

• Set the inverse relationship manually to avoid integral reference issues:

p1.Children.Add(c1);
p1.Children.Add(c2);
c1.Parents.Add(p1);
c2.Parents.Add(p1);

• Set the Child -> Parent relationship as read only:

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

    [ManyToMany(typeof(OnParent), ReadOnly = true)]
    public List<Parent> Parents { get; set; }
}

• Avoid using recursive operations when you're not updating inverse relationships.

• Insert OnParent elements manually.