score:17

Accepted answer

SQLite does not support join via Linq for now as far as I know. I had to use the query syntax like this:

var q = database.Query<MusicItems>(
        @"select MI.Name, MI.ResId, 
             MI.Tension from MusicItems MI inner join MusicInThemes MT on 
             MI.ResId = MT.ResId where MT.ThemeId = ?",
        ThemeID).ToList();
return q.Select(x => new Playlist { Name = x.Name, ResId = x.ResId, LoopStart = x.LoopStart });

score:4

use '@' symbol in front of query will make it look simple and more readable.

 var musicFiles = database.Query<MusicItems>(@"select MI.Name, MI.ResId, 
                 MI.Tension from MusicItems MI inner join MusicInThemes MT on 
                 MI.ResId = MT.ResId where MT.ThemeId = ?",
                 ThemeID).ToList();

Related Query