Xamarin - SQLite Join

5

I'm creating an app on xamarin, which generates a sales order, and stores it on my phone.

I'm using SQLite to store the data, but I'm having trouble joining between my Order Items table and the Products table.

I have both 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 in ITPD by taking the description of the product, in the case inner join tbPROD on PROD_Id = ITPD_PROD_Id. But in SQLite I am not able to do this, another thing also, 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, are as follows:

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 could not follow the link you gave me, in fact, I can not understand how it works.

Could someone please give me an example with these two classes of mine?

Follow below:

CLIE (Clients):

PEDI(Orders)

Selectbelow:

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

    
asked by anonymous 16.06.2016 / 16:34

1 answer

1

SQLite does not support .Join via linq of course. What you can do is to do 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 at official documentation .

Now, if you want to use a third-party library, there is SQLite .Net Extensions , which allows you to use the .Join via linq .

    
16.06.2016 / 16:49