Creating Views

Everyone has heard of views, haven't they? They are useful for storing live queries you might use often or make complex queries less complex. To create a view use the CreateView method in the SQLiteDatabase class.

For example, the Album table has no information about the Tracks in it.
Say you often needed to know the total price of an album and the number of Tracks in it. This information could be stored directly in the Album table but in a similar situation it only makes it a hassle to always update it any time you update Tracks on the Album.

Instead a view could be created for it, that has a query stored to automatically fetch the total price and count when you query from it.

First a creating a model class would make things easier.

public class AlbumView
{
    public int AlbumId { get; set; }
    public string Title { get; set; }
    public int ArtistId { get; set; }
    public decimal TotalPrice { get; set; }
    public int TrackCount { get; set; }
}

This class has no attributes, the same way views do not have column attributes, indexes or table information stored in it. All it remembers is a query.

Now define the View type in your SQLiteDatabase subclass. In this case ChinookDatabase.

public class ChinookDatabase : SQLiteDatabase
{
    // Other tables definitions ....

    public View<AlbumView> AlbumsView { get; set; }

    // Other methods .....
}

Now create the view using the CreateView method.
The first argument is the name of the view.
The second argument is a LINQ query that will be converted to an SQL and stored in the database.

CreateView(
    "AlbumsView",
    Albums.GroupJoin(Tracks, album => album.AlbumId, track => track.AlbumId, (album, tracks) => new AlbumView
    {
        AlbumId = album.AlbumId,
        Title = album.Title,
        ArtistId = album.ArtistId,
        TotalPrice = tracks.Sum(t => t.UnitPrice),
        TrackCount = tracks.Count()
    })
);

After the view is created then you should be able to use it like it were a table.
NOTE: You cannot UPDATE, INSERT or DELETE from a view, since it's really nothing more than a query, unless you really know what you are doing ;).

The library tries to support most queries but some may not be able to be completely converted to SQL. Although most queries you would use should be supported. :D
Worst-case scenario use a method instead :P.