CODEWITHSUNDEEP
androidiossqlitejoinxamarin
Zain SMJ
Zain SMJ

Reputation: 1632

Xamarin SQLite Net - Fetch data from multiple tables

I am working on Xamarin multi-platform native mobile app. I am using sqlite.net plugin for the database.

Sqlite.Net Plugin

I've created three tables:

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

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

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

Upvotes: 2

Views: 1607

Answers (1)

Elvis Xia - MSFT
Elvis Xia - MSFT

Reputation: 10841

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

You can use LinQ to achieve that.

Example:

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

Upvotes: 2

Related Questions