ManyToMany relationship to the same table

Question

I'm trying to add relationship between to categories in the same table. I have a

CATEGORIES (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT)

table and

CATEGORIES_CATEGORIES (ID INTEGER PRIMARY KEY AUTOINCREMENT, CATEGORIES_ID INTEGER NOT NULL, SUBCATEGORIES_ID INTEGER NOT NULL)

table.

And I'm trying to map them together with Vici Coolstorage ORM. I've made classes

Category class

[MapTo("CATEGORIES")]
    public abstract class Category : CSObject<Category>
    {
        public abstract int Id { get; }
        public abstract string Name { get; set; }

        [ManyToMany("CATEGORIES_CATEGORIES", Pure = true)]
        public abstract CSList<CategoryCategory> Subcategories { get; set; }
    }

CategoryCategory class

[MapTo("CATEGORIES_CATEGORIES")]
    public abstract class CategoryCategory : CSObject<CategoryCategory>
    {
        public abstract int Id { get; }

        [ManyToOne(LocalKey = "CATEGORIES_ID", ForeignKey = "ID")]
        public abstract Category Cat { get; set; }
        [ManyToOne(LocalKey = "SUBCATEGORIES_ID", ForeignKey = "ID")]
        public abstract Category SubCat { get; set; }
    }

But I cannot make it work. Can anyone help me to get this relation to work?

Answer 1

Your mapping table should not be the target of a many-to-many relation.

Try something like this:

[ManyToMany("CATEGORIES_CATEGORIES", 
            LocalKey="ID", ForeignKey="ID",  
            LocalLinkKey="CATEGORIES_ID", ForeignLinkKey="SUBCATEGORIES_ID")]
public abstract CSList<Category> Subcategories { get; }