Xamarin - Sqlite Join

Question

I’m creating an app on Xamarin, which generates a sales order, and stores on mobile.

I’m using Sqlite to store the data, but I’m having trouble making a Join between my Order Items table, and the Products table.

I have the two models below:

[Table("tbPROD")]
public class PROD
{
    [PrimaryKey, AutoIncrement]
    public int PROD_Id { get; set; }

    [MaxLength(100)]
    public string PROD_Descricao { get; set; }

    public decimal PROD_Preco { get; set; }
}

[Table("tbITPD")]
public class ITPD
{
    [PrimaryKey]
    public int ITPD_Id { get; set; }

    public int ITPD_PEDI_Id { get; set; }

    public int ITPD_PROD_Id { get; set; }

    public int ITPD_Quantidade { get; set; }

    public decimal ITPD_ValorTotal { get; set; }

    public override string ToString()
    {
        return string.Format("{0} {1} {2} {3}", ITPD_Id, ITPD_PROD_Id, ITPD_Quantidade, ITPD_ValorTotal);
    }
}

I needed to make a select on the ITPD by taking the product description, in the case of Inner Join tbPROD on Prod_id = Itpd_prod_id. But in Sqlite I’m not able to do this, something else too, I can do select mount in Sqlite? Type, "select Prod_id, Prod_descricao from tbPROD".

I’m asking this, because the selects I’m using, this way:

public List<PROD> GetProdutos(string valor)
    {
        List<PROD> list = new List<PROD>();

        try
        {
            list = valor == string.Empty ?
                _SQLiteConnection.Table<PROD>()
                                 .OrderBy(p => p.PROD_Id)
                                 .ToList() :
                _SQLiteConnection.Table<PROD>()
                                 .Where(p => p.PROD_Descricao.Contains(valor))
                                 .OrderBy(p => p.PROD_Id)
                                 .ToList();
        }
        catch (Exception ex)
        {
            HasError = true;
            MsgError = ex.Message;
        }

        return list;
    }

---

@Randrade, I couldn’t follow with the link you gave me, actually, I’m not getting to understand how it works.

Could someone make an example for me, with these two classes of mine?

Goes below:

CLIE (Customers):

PEDI (Requests)

Below is the select:

With the Pedi_clie_description field in the PEDI class, my select works correctly! But what would it look like if I took this field? What modeling and select would look like?

Answer 1

Sqlite does not support .Join via linq of course. What you can do is make the consultation in hand.

_SQLiteConnection.Query<PROD>(
        "select * from tbPROD p INNER JOIN tbITPD pd on p.PROD_Id = pd.ITPD_PROD_id").ToList();

More details can be seen on official documentation.

Now, if you want to use a third-party library, there is a Sqlite . Net Extensions, which makes it possible to use the .Join via linq.