Xamarin SQLite Net - Fetch data from multiple tables

Question

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

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

Elvis Xia - MSFT · Accepted Answer · 2017-09-18 09:15:17Z

2

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

answered Sep 18, 2017 at 9:15

Elvis Xia - MSFT

10.9k1 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Xamarin SQLite Net - Fetch data from multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related