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?

What error is giving you? At which line? Are you using SQLite-Net Extensions MvvmCross package (https://www.nuget.org/packages/SQLiteNetExtensions-MvvmCross/)? — redent84, Nov 05 '14 at 09:21
That is not quite my code but tried to rework the example from your website so that we were all on the same page. Basically I have a parent class which holds a list of categories which hold a list of valuations. When I click on the parent class I'm trying to do a Grouped iOS table view where the category description is displayed as the header row and the children valuations are rows under that. In order to create my MvxTableViewSource for the table I needed the data in List form for the ItemsSource. I think having an IEnumerable will get me far enough! — PkL728, Nov 05 '14 at 15:44
Offtopic, but take a look at http://forums.xamarin.com/discussion/22913/similar-to-the-expandable-list-view-in-android to see MvvmCross code of collapsable (or not) multi-section tables using MvvmCross. You don't even need inheriting `IEnumerable` this way. — redent84, Nov 05 '14 at 17:07

score 3 · Accepted Answer · answered Nov 05 '14 at 09:33

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

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

1 Answers1