Return children using a Where clause

Question

I'm using the SQL-NET Extensions in my Xamarin project. I am trying to return children emelments of my model using a where clause. Using the example models on the website:

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

    [OneToMany(CascadeOperations = CascadeOperation.All)]      // 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 can succesfully return a specific item with the children populated using:

var storedValuation = db.GetWithChildren<Valuation>(valuation.Id);

However I can't work out how to do it using a Where clause instead of Get. I have tried:

var results = db.Table<Valuation>().Where(x=>x.Price > 5.0m).ToList();

This returns with all the Stock parameters being null. I could then loop through each result and set them, but I assume there must be a better way to do it in the original query?

redent84 · Accepted Answer · 2014-09-15T16:33:14.377

You can obtain the relationships for any object calling GetChildren method:

var results = db.Table<Valuation>().Where(x=>x.Price > 5.0m).ToList();
foreach (var element in results) {
    conn.GetChildren(element);
}

There's also a convenience method for querying the database called GetAllWithChildren that performs the same operation in a less verbose way:

var results = conn.GetAllWithChildren<Valuation>(x => x.Price > 5.0m).ToList();

Please take into account that you cannot access relationships in this query as they would require a JOIN that is not being performed. For simple queries like this it should work as expected.

Return children using a Where clause

1 Answers1