How do I query a View in SQLite.net?

问题:

I am writing a mobile app using Xamarin.Forms PCL in Visual Studio with a local SQLite database. I am using the SQLite.net-PCL Async plugin because sqlite-net-pcl kept crashing my app.

I am able to query tables fine, but I'm not able to figure out how to query views.

How I am successfully querying a table

Model

[Table("pitchers")]
public class Pitcher
{
    [PrimaryKey, AutoIncrement, Column("id")]
    public ushort Id { get; set; }

    [Column("first_name")]
    public string FirstName { get; set; }

    [Column("last_name")]
    public string LastName { get; set; }

    [Column("uniform_number")]
    public byte UniformNumber { get; set; }
}

Query code

public Task<List<Pitcher>> GetAllPitchersAsync()
{
    return dbConn.Table<Pitcher>().OrderBy(i => i.LastName).ToListAsync();
}

I have a view called "joined_pitcher_log" that I am trying to query using the same method. I created a model for it and used the same language (except for order by) to query, but I keep getting the error:

SQLite.Net.SQLiteException: no such table: joined_pitcher_log

Things I tried

1) remove the table attribute from the model I created for the view. There is no view attribute to replace it with.

2) use dbConn.QueryAsync instead of dbConn.Table to query the view directly using SQL.

3) looked for other properties and methods for dbConn. There is nothing related to Views.

How do I query Views?

Update: A workaround if there is no way to query Views

I could also write the SQLite View's query in my code, but then I wouldn't know how to reference multiple models, which is a different issue. I'll take either solution at this point (querying a view or writing a query on multiple tables/models that I'm joining)

回答1:

The only way to get data from a view is to use SQLite-Net's Query or QueryAsync method. Just create a class to hold the returned data.

db.Query<MyReturnType> ("select * from MyView");

or

await db.QueryAsync<MyReturnType> ("select * from MyView");