Join tables in SQLite.Net with LINQ on xamarin.android is not supported

Question

i have stuck in a join and i cant figure out where the problem is, i have those tables

public class Themes
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public String ThemeName { get; set; }
    public String ThemeDesc { get; set; }
    public int ThemeImg { get; set; }
    public String ThemeCategory { get; set; }
    public String ThemeSubcategory { get; set; }
}
public class MusicItems
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public String Name { get; set; }
    public String Tension { get; set; }
    public String Category { get; set; }
    public String Subcategory { get; set; }
    public int ResId { get; set; }
    public int LoopStart { get; set; }
}
public class Playlist
{
    public String Name { get; set; }
    public int ResId { get; set; }
    public int LoopStart { get; set; }
}
public class MusicInThemes
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public int ResId { get; set; }
    public int ThemeId { get; set; }
}

And i want to join MusicItems with MusicInThemes in order to get all the music file details that belongs on a theme. So far i have try these that crash my app:

                var query = from MusicItem in database.Table<MusicItems>() 
                    join Theme in database.Table<MusicInThemes>() 
                    on MusicItem.ResId equals Theme.ResId
                    where Theme.ThemeId == ThemeID
                    select new Playlist{Name = MusicItem.Name, ResId = MusicItem.ResId ,LoopStart = 0};
                return query.ToList();

 // and
return database.Table<MusicItems>() 
                    .Join(database.Table<MusicInThemes>().Where(t => t.ThemeId == ThemeID)
                        ,m => m.ResId
                        ,t => t.ResId
                        ,(m,t) => new Playlist{Name = m.Name, ResId = m.ResId ,LoopStart = m.LoopStart})
                    .ToList();

And this is the code where i get the Playlist: (I have 2 buttons. The btnTheme1 display some info for the theme and save som theme variables and then the loadTheme button tries to extract the songs from the selected theme)

ThemeImage = view.FindViewById<ImageView> (Resource.Id.imgThemeImage);
        ThemeDesc = view.FindViewById<TextView> (Resource.Id.txtDesc);

        Button btnTheme1 = view.FindViewById<Button> (Resource.Id.btnTheme1);
        //Button btnTheme2 = view.FindViewById<Button> (Resource.Id.btnTheme2);
        Button btnLoadTheme = view.FindViewById<Button> (Resource.Id.btnLoadTheme);

        musicFiles.AllThemes = mydata.GetAllThemes ();
        Console.WriteLine("Themes Count:"+musicFiles.AllThemes.Count);


        var ListSEbuttons = new List<Button> {
            { btnTheme1 },
        };
        for (i=0;i<ListSEbuttons.Count();i++){
            var button = ListSEbuttons[i];
            button.Text = SelectedThemeName = musicFiles.AllThemes [i].ThemeName;
            SelectedThemeImageId = musicFiles.AllThemes [i].ThemeImg;
            button.Tag = i;
            SelectedThemeId = musicFiles.AllThemes [i].Id;
            button.Click += ButtonOnClick;
        }

        btnTheme1.PerformClick ();

        btnLoadTheme.Click += (sender, e) => {
        Low = mydata.GetPlaylist(SelectedThemeId,"low"); // this is where the crash happens
        ...
        }

    private void ButtonOnClick(object sender, EventArgs eventArgs)
    {
        var button = sender as Button;
        if (button != null)
        {
            var index = (int)button.Tag;
            Console.WriteLine("List pos for sound effect: "+musicFiles.AllThemes [index].ThemeImg);

            button.RequestFocus();
            ThemeDesc.Text = musicFiles.AllThemes [index].ThemeDesc;
            //ThemeImage.SetImageResource (musicFiles.AllThemes [index].ThemeImg);

        }
    }
//ignore the "low" parameter i never use it for now

Please advice

Debug Log:
...
[Mono] Assembly Ref addref Music[0xb95c8210] -> Newtonsoft.Json[0xb95ca100]: 2
[Mono] The request to load the retargetable assembly mscorlib v2.0.5.0 was remapped to mscorlib v2.0.5.0
[Mono] Assembly Ref addref Newtonsoft.Json[0xb95ca100] -> mscorlib[0xb9406208]: 7
[Mono] Assembly Ref addref System.Core[0xb960c248] -> System[0xb972dd18]: 4
[MediaPlayer-JNI] MediaPlayer finalized without being released
[MediaPlayer-JNI] MediaPlayer finalized without being released
[MediaPlayer-JNI] MediaPlayer finalized without being released
[MediaPlayer-JNI] MediaPlayer finalized without being released
[Mono] GC_OLD_BRIDGE num-objects 44 num_hash_entries 47 sccs size 47 init 0.00ms df1 0.19ms sort 0.30ms dfs2 1.39ms setup-cb 0.01ms free-data 0.12ms links 3/3/3/1 dfs passes 94/50
[Mono] GC_MINOR: (Nursery full) pause 19.83ms, total 19.96ms, bridge 11.96ms promoted 784K major 1072K los 8K
[Mono] GC_OLD_BRIDGE num-objects 44 num_hash_entries 47 sccs size 47 init 0.00ms df1 0.19ms sort 0.30ms dfs2 1.39ms setup-cb 0.01ms free-data 0.12ms links 0/0/0/0 dfs passes 0/0
[Mono] GC_MINOR: (Nursery full) pause 11.56ms, total 11.67ms, bridge 0.17ms promoted 368K major 1440K los 8K
[] * Assertion at /Users/builder/data/lanes/monodroid-mlion-monodroid-4.20-series/ba9bbbdd/source/mono/mono/metadata/sgen-stw.c:68, condition `info->stack_start >= info->stack_start_limit && info->stack_start < info->stack_end' not met
[mono-rt] Stacktrace:
[mono-rt] 
[mono-rt]   at <unknown> <0xffffffff>
[mono-rt]   at (wrapper managed-to-native) object.__icall_wrapper_mono_array_new_specific (intptr,int) <IL 0x00022, 0xffffffff>
[mono-rt]   at System.Collections.Concurrent.SplitOrderedList`2<string, System.Collections.Generic.KeyValuePair`2<string, SQLite.PreparedSqlLiteInsertCommand>>..ctor (System.Collections.Generic.IEqualityComparer`1<string>) <IL 0x00006, 0x0004b>
[mono-rt]   at System.Collections.Concurrent.ConcurrentDictionary`2..ctor (System.Collections.Generic.IEqualityComparer`1<TKey>) <IL 0x0000f, 0x000a6>
[mono-rt]   at System.Collections.Concurrent.ConcurrentDictionary`2..ctor () <IL 0x00006, 0x0006b>
[mono-rt]   at SQLite.TableMapping..ctor (System.Type,SQLite.CreateFlags) [0x0019d] in c:\Users\Exoskeletor\Documents\Projects\Music\Music\Helpers\SQLite.cs:1755
[mono-rt]   at SQLite.SQLiteConnection.GetMapping (System.Type,SQLite.CreateFlags) [0x00036] in c:\Users\Exoskeletor\Documents\Projects\Music\Music\Helpers\SQLite.cs:304
[mono-rt]   at SQLite.TableQuery`1..ctor (SQLite.SQLiteConnection) [0x00010] in c:\Users\Exoskeletor\Documents\Projects\Music\Music\Helpers\SQLite.cs:2473
[mono-rt]   at SQLite.SQLiteConnection.Table<T> () [0x00001] in c:\Users\Exoskeletor\Documents\Projects\Music\Music\Helpers\SQLite.cs:768
[mono-rt] at Music.DataManager.DatabaseUpdates.GetPlaylist (int,string) [0x00027] in c:\Users\Exoskeletor\Documents\Projects\Music\Music\Helpers\DataManagerHelper.cs:288
[mono-rt] at Music.Music_SelectTheme/<>c__DisplayClass2.<OnCreateView>b__1 (object,System.EventArgs) [0x00001] in c:\Users\Exoskeletor\Documents\Projects\Music\Music\Music_SelectTheme.cs:77
[mono-rt] at Android.Views.View/IOnClickListenerImplementor.OnClick (Android.Views.View) [0x0000d] in /Users/builder/data/lanes/monodroid-mlion-monodroid-4.20-series/ba9bbbdd/source/monodroid/src/Mono.Android/platforms/android-15/src/generated/Android.Views.View.cs:1615
[mono-rt] at Android.Views.View/IOnClickListenerInvoker.n_OnClick_Landroid_view_View_ (intptr,intptr,intptr) [0x00011] in /Users/builder/data/lanes/monodroid-mlion-monodroid-4.20-series/ba9bbbdd/source/monodroid/src/Mono.Android/platforms/android-15/src/generated/Android.Views.View.cs:1582
[mono-rt] at (wrapper dynamic-method) object.a2a00f7c-dc24-4757-ba0d-9fa6ea56b30a (intptr,intptr,intptr) <IL 0x00017, 0x0001f>
[mono-rt] at (wrapper native-to-managed) object.a2a00f7c-dc24-4757-ba0d-9fa6ea56b30a (intptr,intptr,intptr) <IL 0x00023, 0xffffffff>
[mono-rt] 
[mono-rt] =================================================================
[mono-rt] Got a SIGSEGV while executing native code. This usually indicates
[mono-rt] a fatal error in the mono runtime or one of the native libraries 
[mono-rt] used by your application.
[mono-rt] =================================================================
[mono-rt] 
[libc] Fatal signal 11 (SIGSEGV) at 0xdeadbaad (code=1), thread 1385 (Music.Music)

ok the app crashes that not good what's also not good is that you do not give any details in regards to `Error mesages` can you provide more info — MethodMan, Dec 02 '14 at 23:27
ok this is the debug log from Xamarin, i don't know if this is enough http://jpst.it/uOOg — CDrosos, Dec 03 '14 at 01:15
added the stacktrace of the compiled SQLite source, instead of the SQLite dll stacktrace — CDrosos, Dec 09 '14 at 00:58

CDrosos · Accepted Answer · 2020-08-04T13:14:16.353

17

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 });

edited Aug 04 '20 at 13:14

answered Dec 10 '14 at 22:42

CDrosos

2,418
4
26
49

what is ConvertAll, is it method in your database? – Gagan_iOS Apr 24 '17 at 07:24
1

@Gagan_iOS ConvertAll is a c# method that convert a list to another list, see an example here: http://www.c-sharpcorner.com/UploadFile/dhananjaycoder/convert-lista-to-listb-using-convertall-in-C-Sharp/ – CDrosos Apr 24 '17 at 08:53
1

Thanks CDrosos, I am a beginner in C# – Gagan_iOS Apr 24 '17 at 12:22
`ConvertAll` was introduced in `.Net 2.0`, `Select` was introduced in `.Net 3.5`. Rather use `Select`. Check out this SO post https://stackoverflow.com/a/1571843/1876355 – Pierre Jul 02 '18 at 06:23
This syntax , now not woking? database.Query not exist, only have database.QueryAsync, but it show error not exist ToList() – D T Aug 20 '18 at 06:47

score 4 · Answer 2 · answered Apr 16 '18 at 12:21

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();

answered Apr 16 '18 at 12:21

Keerthi

525
8
14

2

More details about @ can be found also here: https://stackoverflow.com/questions/556133/whats-the-in-front-of-a-string-in-c – CDrosos Apr 16 '18 at 21:48

Join tables in SQLite.Net with LINQ on xamarin.android is not supported

2 Answers2

Linked