Querying Data

Querying data from the database is pretty straight forward using the created models.
The Table and View classes implement IQueryable, which means a whole lot of methods are available for use.
Most of these methods are fully supported including:

• Select
• Where
• SelectMany
• Join
• GroupJoin
• OrderBy
• OrderByDescending
• ThenBy
• ThenByDescending
• GroupBy
• Count
• LongCount
• Min
• Max
• Sum
• Average
• Distinct
• Skip
• Take
• ElementAt
• ElementAtOrDefault
• First
• FirstOrDefault
• Single
• SingleOrDefault
• Any
• All
• Contains
• Cast

In the event that you need to write a raw SQL query, if it is not supported by LINQ or the library. Then you have the option of using the Query method in the SQLiteDatabase class.

using (var db = new ChinookDatabase())
{
    var result = db.Query<Album>("SELECT * FROM [Album] ORDER BY RANDOM()");
    foreach (var item in result)
    {
        // Other stuf ........
    }
}

This can be done as long as the Properties of the specified class are correctly mapped the names of the columns returned by the query, either by name or attribute.

If you can't or don't want to properly map the properties to the columns or you want to map them to fields or constructor arguments or to some weird class member or something. Well you are stuck and nothing can be done about that.... Just kidding!!! You can use a different overload of the Query method.

using (var db = new ChinookDatabase())
{
    var result = db.Query("SELECT * FROM [Album] ORDER BY RANDOM()", r => new
    {
        Id = r.Get<int>("AlbumId"),
        Title = r.Get<string>("Title")
    });
    foreach (var item in result)
    {
        // Other stuf ........
    }
}

But you must know the name of the columns being returned. Shouldn't be a too much problem.

Also if you want to use LINQ but only a tiny part of the query has to be typed manually like above, then you can use LINQ on the result of the query and it will be sent to the database.
Although, this is limited to public properties and not 'weird' members or constructor arguments.

using (var db = new ChinookDatabase())
{
    var result = db.Query("SELECT * FROM [Album] ORDER BY RANDOM()", r => new
    {
        Id = r.Get<int>("AlbumId"),
        Title = r.Get<string>("Title")
    })
    .Where(a => 9 < a.Id && a.Id < 100).Select(a => a.Title);
    foreach (var item in result)
    {
        // Other stuf ........
    }
}