android - Xamarin SQLite Net - 从多个表中获取数据

Question

我正在开发 Xamarin 多平台 native 移动应用程序。我正在为数据库使用 sqlite.net 插件。

Sqlite.Net Plugin

我创建了三个表:

public class OrderT
{
    SQLiteConnection database;

    [PrimaryKey, AutoIncrement]
    public int id { get; set; }
    public int client_id { get; set; }
    public DateTime order_date { get; set; }
}

public class OrderDetailT
{
    SQLiteConnection database;

    [PrimaryKey, AutoIncrement]
    public int id { get; set; }
    public int order_id { get; set; }
    public int quantity { get; set; }
    public float price { get; set; }
}

public class ClientT
{
    SQLiteConnection database;

    [PrimaryKey, AutoIncrement]
    public int id { get; set; }
    public string client_name { get; set; }
}

如何在 Xamarin Sqlite 中运行这样的查询？由于 Xamarin Sqlite 中没有关系和外键的概念。

SELECT OrderT.*, OrderDetailT.*, ClientT.*
FROM OrderT, OrderDetailT, ClientT
WHERE OrderT.client_id = ClientT.id AND OrderT.id = OrderDetailT.order_id

Answer 1

How can I run a query like this in Xamarin Sqlite? Since there is no concept of relationships and foreign key in Xamarin Sqlite

您可以使用 LinQ实现这一目标。

例子:

var list = from orderT in db.Table<OrderT>()
                  from clientT in db.Table<ClientT>()
                  from orderDetailT in db.Table<OrderDetailT>()
                  where orderT.client_id == clientT.id
                  where orderT.id == orderDetailT.order_id
                  select new { client_name=clientT.client_name, client_id = orderT.client_id, order_id = orderDetailT.order_id,price=orderDetailT.price, quality=orderDetailT.quantity };