Many to Many “self”-relation SQLite: is it possible?

[Alex]

First of all thank you very much for the library. I would like to create a many to many self-relation. In my case I have a child class with some siblings, of course the siblings are still of child type. Therefore I tried to implement a manytomany relation:

Child Class

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

Brotherhood Class

class Brotherhood
{
  [ForeignKey(typeof(Child))]
  public int ID_child1 { get; set; }

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

}

That should be all the work. Then I create a Child and add a sibling in the Siblings list, but when I try to save the class in the DB using InsertOrReplaceWithChildren(Child,true) only ID_child1 is updated and ID_child2 stay to 0. Any idea? What am I doing wrong? Alex

[Guillermo Gutiérrez]

For this kind of questions please use StackOverflow so it can benefit future readers.

In this scenario you have to explicitly specify foreign keys and inverse relationships in the property attribute.

You have a complete sample for this in the tests code:

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

        public string Name { get; set; }

        [ManyToMany(typeof(FollowerLeaderRelationshipTable), "LeaderId", "Followers",
            CascadeOperations = CascadeOperation.All)]
        public List<TwitterUser> FollowingUsers { get; set; }

        // ReadOnly is required because we're not specifying the followers manually, but want to obtain them from database
        [ManyToMany(typeof(FollowerLeaderRelationshipTable), "FollowerId", "FollowingUsers",
            CascadeOperations = CascadeOperation.CascadeRead, ReadOnly = true)]
        public List<TwitterUser> Followers { get; set; }
    }

    // Intermediate class, not used directly anywhere in the code, only in ManyToMany attributes and table creation
    public class FollowerLeaderRelationshipTable {
        public int LeaderId { get; set; }
        public int FollowerId { get; set; }
    }

[Guillermo Gutiérrez]

Apart from that, don't use InsertOrReplace with AutoIncrement primary key. It will always assign and replace element with ID 0.

[Alex]

Thanks for the fast answer. I posted also the question on stackoverflow (here the link. I didn't succeed in following the example, so I have broken the "relationship": instead of having manytomany relation with the same class I have a onetoone relation with a support class and then a manytomany relation with the first class. That works! Now I'm fighting a bit in finding the best way to insert and update everything. I don't have encountered particular problems in using InsertOrReplace (if I add no more than one element each time in each table should work fine), but if the second element has some objects in common with the first one in the relationship table the old line is replaced with the "new" one. Let's say A has two brothers B and C, and D has one brother C. Using insertorrplave with recursive:true I just see that A has B and D has C.

[Alex]

After some attempts I managed in realising the many to many self relation, I was just a bit confused on having two manytomany relation in the same class: I was wondering which was the "official" one. Eventually I understood that both were! And the sum of the siblings was the sum of the two Lists

[ManyToMany(typeof(Brotherhood), "ChildID1", "Siblings_DB_support", CascadeOperations = CascadeOperation.All)]
        public List<Child> Siblings_DB { get; set; }
[ManyToMany(typeof(Brotherhood), "ChildID2", "Siblings_DB", CascadeOperations = CascadeOperation.All)]
        public List<Child> Siblings_DB_support { get; set; }

[Guillermo Gutiérrez]

Well, you have two ManyToMany that are reverse to each other, one with the children, and one with the parents. Maybe it's clearer in the Twitter example: one with your followers, and other with the users that follow you.

Depending on the use case, it's usually a good idea to leave one as 'ReadOnly', because normally you'll only set one of the relationships and the other one is 'discovered'.

For example, you don't specify the users that follow you, you only set your followers, and the users that follow you are 'discovered' by reversing the relationship.