MVVMCross Community SqLite - Relationship between tables

mvvmcross, sqlite, sqlite-net

Solution

SQLite-net only provides cross-table referencing using indexing like:

public class Stock
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [MaxLength(8)]
    public string Symbol { get; set; }
}

public class Valuation
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [Indexed]
    public int StockId { get; set; }
    public DateTime Time { get; set; }
    public decimal Price { get; set; }
}

There is at least one extension to sqlite-net which allows `OneToMany` attributes to be declared - see https://bitbucket.org/twincoders/sqlite-net-extensions which enables code like:

public class Stock
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [MaxLength(8)]
    public string Symbol { get; set; }

    [OneToMany]      // One to many relationship with Valuation
    public List<Valuation> Valuations { get; set; }
}

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

    [ForeignKey(typeof(Stock))]     // Specify the foreign key
    public int StockId { get; set; }
    public DateTime Time { get; set; }
    public decimal Price { get; set; }

    [ManyToOne]      // Many to one relationship with Stock
    public Stock Stock { get; set; }
}

I'm not sure of the exact implementation of this - e.g. I don't know if this uses real `FOREIGN KEY` constraints - but the code is open source, is under active development, has mvvmcross plugin support built-in, is cross platform and is available for forking and for contributions.

Problem

I have two simple tables as follow: ``` public class MediaPartner { [PrimaryKey, AutoIncrement] public int Id { get; set; } public string PhoneNumber { get; set; } public string CompanyName { get; set; } public double Lat { get; set; } public double Lng { get; set; } public DateTime InsertedUtc { get; set; } } public class ImageGroup { [PrimaryKey, AutoIncrement] public int Id { get; set; } public List<MediaPartner> IdMediaPartner { get; set; } public string ImagePath { get; set; } public bool IsSent { get; set; } public DateTime InsertedUtc { get; set; } } ``` The problem: public List< MediaPartner > IdMediaPartner { get; set; } OR public MediaPartner IdMediaPartner { get; set; } does not compile. My question is: Is there a way to build one-to-many relationship between these two tables? Thank you!

Original source