SQLite join in embedded database tables

Question

I am currently writing a Windows Store Application for Windows 8 and I am using SQLite for the persistence on the embedded database in a class library for windows apps. I am trying to join data from two different tables in my embedded database but SQLite keeps throwing a not supported exception from their GenerateCommand method.

I do currently have data in both tables and they both have a questionId in each table to join on. I've tried two different methods which both throw the same error.

The first method:

    var q = (from gameTable in db.Table<Model.GameSaved>()
                 join qTable in db.Table<Questions>() on gameTable.QuestionId equals qTable.QuestionId
                 select qTable
                ).First();

The second method:

    var q =
                (from question in db.Table<Model.GameSaved>()
                 select question
                ).Join(db.Table<Questions>(), 
                       game => game.QuestionId, 
                       questionObject => questionObject.QuestionId,
                       (game,questionObject) => questionObject)
                 .First();

I'm not exactly sure what I'm missing here but it has to be something simple and obvious.

Foreworn

Answer 1

You are not missing anything. Joins via linq are not supported by Sqlite-net at this time. You can work around this by providing your own SQL and using the Query method. Your first query from above would look something like:

var q = db.Query<Questions>(
    "select Q.* from Questions Q inner join GameSaved G"
    + " on Q.QuestionId = G.QuestionId"
).First();

If you are so inclined, the Query method also supports parameters. From the Sqlite-net readme:

db.Query<Val>(
    "select 'Price' as 'Money', 'Time' as 'Date' from Valuation where StockId = ?",
     stock.Id);

Cacique

Thank you for your answer! It seemed like SQLite-net didn't support joins because it didn't make sense but now I understand the restrictions more. – Foreworn