如何在SQLite.net中查询视图?

问题描述

我正在Visual Studio中使用Xamarin.Forms PCL和本地SQLite数据库编写移动应用程序.我正在使用SQLite.net-PCL异步插件，因为sqlite-net-pcl一直使我的应用程序崩溃.

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.

[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; }
}

查询代码

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

我有一个名为"joined_pitcher_log"的视图，我试图使用相同的方法进行查询.我为此创建了一个模型，并使用相同的语言(order by除外)进行查询，但是我不断收到错误消息:

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

我尝试过的事情

1)从我为视图创建的模型中删除表属性.没有视图属性可以替换为该属性.

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)使用 dbConn.QueryAsync 代替 dbConn.Table 来直接使用SQL查询视图.

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

3)寻找dbConn的其他属性和方法.没有与视图相关的信息.

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

如何查询视图?

我也可以在代码中编写SQLite View的查询，但是那时我不知道如何引用多个模型，这是一个不同的问题.此时，我将采用两种解决方案(查询视图或对要加入的多个表/模型进行查询)

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)

推荐答案

从视图获取数据的唯一方法是使用SQLite-Net的Query或QueryAsync方法.只需创建一个类来保存返回的数据即可.

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");

或

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

这篇关于如何在SQLite.net中查询视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！