SQLite Join Query on Two Tables in Xamarin

Question

I am new in Xamarin forms & currently developing an app providing offline data access. For offline feature I have created SQLite database & mulitple tables. Now when I am trying to fetch data using Join, I am unable to perform the task. Below is the code

public Thoughts GetQueryForAllRecords(int ID)
{
    var q = connection.Query<Thoughts>("SELECT Thought,CreatedOn, AssignedToName, AssignedOn FROM Thoughts JOIN AssignedThought ON Thoughts.ID = AssignedThought.ID where Thoughts.ID=?;", ID);

    return q.Last(t => t.ID == ID);
}

'Thoughts' and 'AssignedThought' are two tables. In above code, i am only getting records for one table i.e. Thoughts. If I am changing generic type to 'AssignedThought' then it shows me only AssignedThought records.

Can any one suggest me what am I doing wrong here? I want to perform join operation to get records from both table.

Your join looks like it's done correctly. Without seeing the table schema, I would guess that your problem is with Thoughts.ID = AssignedThought.ID. Typically an ID column is the primary key of a table, but it sounds like AssignedThought is a join table, and you should be joining in your Thoughts table on a foreign key. If you have a column like AssignedThought.ThoughtID, you should probably be joining on that column, instead of AssignedThought.ID. If you are still trying to fix this problem, can you post the table schema? — RToyo

Renjith Renjith · Accepted Answer · 2017-05-04T06:45:48

Does both tables have same properties? Else you have to create a DTO Table with required properties from both the tables and use it as generic type

SQLite Join Query on Two Tables in Xamarin

1 Answers