Support for inferred Foreign Keys

[ShawnA]

Are there any thoughts on adding support for "inferred" foreign keys.

Consider the following sample. It removes the need to add the "Extra properties" to the model just to support the foreign keys. The idea would be to allow the foreign key attribute on a complex property and then specify not only the type (which could be inferred) but also the property on that type that stores the id for the related item.

public class Folder {
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    [OneToMany(inverseProperty: "Parent")]
    [ForeignKey(typeof(Folder), foreignKeyProperty: "ID")]
    public List<Folder> SubFolders { get; set; }

    [ManyToOne(inverseProperty: "SubFolders")]
    [ForeignKey(typeof(Folder), foreignKeyProperty: "ID")]
    public Folder Parent { get; set; }

    [OneToMany]
    [ForeignKey(typeof(Folder), foreignKeyProperty: "ID")]
    public List<File> Files { get; set; }
}

public class Folder {
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    [ManyToOne]
    [ForeignKey(typeof(Folder), foreignKeyProperty: "ID")]
    public Folder Parent { get; set; }
}

[Guillermo Gutiérrez]

The main issue is that this way we would break compatibility with vanilla SQLite-Net because the mapping changes, and all the standard SQLite-Net methods won't work anymore. In addition, we may have OneToMany relationships, where the foreign key is declared in the other entity. This would make impossible to infer foreign keys within the class alone, and we would need the complete entity tree to discover foreign keys, which makes much more difficult even the simplest operation.

Overall, I see this change to add a very thick layer of complexity while adding little functionality and breaking SQLite-Net compatibility.

[ShawnA]

Fair point on the need for support in the core library. Although I have an idea on that, what if we just extended the ForeignKeyAttribute and then extended the CreateTable. My thought is not to get of the foreign key column at the database layer, only the change the mapping in the .,NET code to make to a property on a complex object.