Many to Many "self"-relation SQLite: is it possible?

Question

I'm using SQLite.net and SQLite-Net Extensions. 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

same question here ..check the following : http://stackoverflow.com/questions/14227468/in-sqlite-how-to-implement-a-many-to-many-relationship — Mohamad Mahmoud Darwish, Feb 17 '17 at 14:51

score 0 · Answer 1 · edited May 23 '17 at 12:16

The database schema must first be compatible with what you are trying to do. One side of every relation must be a Key (unique) attribute or set of attributes, so no you cannot have a many-to-many self relation by itself. But if you create another table to hold the related rows, with a combined key that contains two copies of the key attribute[s] from the one table you are trying to create the relation on, then you could do it.

say the table key is myPK, then create a second table named, say myM2M with a two-column Primary Key containing attributes myPK1 and myPK2, which together form the pk of this new table, then form a relationship between both of these columns with the myPK column in the first table.

Have you looked at this ?

My scope is to use the ORM to create this relation and not the raw SQLite language. Everything works if the relation many to many is done between two different classes. Furthermore I have, already, created the "second" table: brotherhood, that should realise the many to many relation. Something is wrong when I use the same class. I will look at the example thx — Alex Gimondi, Feb 17 '17 at 15:21

Alex Gimondi · Accepted Answer · 2017-02-22T15:05:55.873

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; }

And the relationship table:

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

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

score 0 · Answer 3 · answered Feb 24 '17 at 14:26

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

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; }
}

If your relationship is a "brother-brother" instead of "parent-children" you'll have to sum both relationships, for example:

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

    public string Name { get; set; }

    [ManyToMany(typeof(BrothersRelationshipTable), "OldBrotherId", "YoungerBrothers",
        CascadeOperations = CascadeOperation.All)]
    public List<Person> OlderBrothers { get; set; }

    [ManyToMany(typeof(BrothersRelationshipTable), "YoungBrotherId", "OlderBrothers",
        CascadeOperations = CascadeOperation.CascadeRead, ReadOnly = true)]
    public List<Brothers> YoungerBrothers { get; set; }

    [Ignore]
    publc List<TwitterUser> Brothers { 
        get { return YoungerBrothers.Concat(OlderBrothers).ToList() }
    }
}

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

Many to Many "self"-relation SQLite: is it possible?

3 Answers3