How to set up an A -> B -> C relationship using SQLite-Net Extensions

Question

I'm using MVVMCross and the SQLite-Net Extensions package to accomplish the following:

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

    public string Description { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]      // One to many relationship with Valuation
    public List<Stock> Stocks { get; set; }
}

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

    [ForeignKey(typeof(StockGrouping))]
    public int StockGroupingId { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]      // One to many relationship with Valuation
    public List<Valuation> Valuations { get; set; }
#region IList implementation
#endregion
}

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

Basically I have an overall structure where I have a grouping of Stock Categories which have stocks which have valuations.

I am setting this up with the idea of using the iOS Grouped Table View to have stocks grouped under a specific category.

Is this kind of setup possible using SQLite-Net Extensions?

At this point I'm getting an exception when I create my SQLite tables like so:

    private void CreateDataBase()
    {
        sqLiteConnection.CreateTable<Stock>();
        sqLiteConnection.CreateTable<StockGrouping>();
        sqLiteConnection.CreateTable<Valuation>();
    }

It fails on the sqLiteConnection.CreateTable() line saying it doesn't understand StockGrouping.

Any ideas? Is this even possible to do?

Answer 1

It seems that SQLite-Net doesn't support objects inheriting from IList. Changing Stock definition from:

public class Stock : IList<Valuation>

to

public class Stock

made it work in my tests.

You can also inherit from IEnumerable instead of IList and it works:

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

    [ForeignKey(typeof(StockGrouping))]
    public int StockGroupingId { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)]      // One to many relationship with Valuation
    public List<Valuation> Valuations { get; set; }


    public IEnumerator<Valuation> GetEnumerator() {
        return Valuations.GetEnumerator();
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() {
        return Valuations.GetEnumerator();
    }
}