12 Replies - 3856 Views - Last Post: 27 March 2015 - 11:21 AM Rate Topic: -----

#1 cdrosos   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 20-August 14

join tables in LINQ with Lambda expressions

Posted 05 December 2014 - 01:35 PM

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
					return database.Table<MusicItems>() 
						.Join(database.Table<MusicInThemes>().Where(t => t.ThemeId == ThemeID)
							,m =>m.ResId
							,t => t.ResId
							,(m,t) => new {mym = m, myt = t })
						.Select(a => new Playlist
							{
								Name             = a.mym.Name,
								ResId              = a.mym.ResId,
								LoopStart = 0
							})  
						.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.Generic.List`1..ctor (int) <IL 0x0001a, 0x00097>
[mono-rt] at System.MonoCustomAttrs.GetCustomAttributes (System.Reflection.ICustomAttributeProvider,System.Type,bool) <IL 0x00248, 0x00b63>
[mono-rt] at System.MonoType.GetCustomAttributes (System.Type,bool) <IL 0x0001a, 0x0008b>
[mono-rt] at SQLite.TableMapping..ctor (System.Type,SQLite.CreateFlags) <IL 0x00019, 0x0008d>
[mono-rt] at SQLite.SQLiteConnection.GetMapping (System.Type,SQLite.CreateFlags) <IL 0x00030, 0x0011f>
[mono-rt] at SQLite.TableQuery`1..ctor (SQLite.SQLiteConnection) <IL 0x0001f, 0x00093>
[mono-rt] at SQLite.SQLiteConnection.Table<T> () <IL 0x00001, 0x0004f>
[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)


Is This A Good Question/Topic? 0
  • +

Replies To: join tables in LINQ with Lambda expressions

#2 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7056
  • View blog
  • Posts: 23,992
  • Joined: 05-May 12

Re: join tables in LINQ with Lambda expressions

Posted 06 December 2014 - 12:19 AM

Based on that callstack, the issue seems to be with SQLite. Since SQLite is open source, why not dig into that code to see why it is crashing and then back track into your C# code to fix the issue?
Was This Post Helpful? 0
  • +
  • -

#3 cdrosos   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 20-August 14

Re: join tables in LINQ with Lambda expressions

Posted 06 December 2014 - 02:34 AM

how i can do that?it seems very generic error to me. how i can find where the problem is?
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7056
  • View blog
  • Posts: 23,992
  • Joined: 05-May 12

Re: join tables in LINQ with Lambda expressions

Posted 06 December 2014 - 09:06 AM

The error itself maybe generic, but the context is not. You need to determine what set of conditions sets up that context. Since it is open source, you have the luxury of actually being able to trace into code instead of having to guess based on a static callstack and code inspection.
Was This Post Helpful? 0
  • +
  • -

#5 cdrosos   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 20-August 14

Re: join tables in LINQ with Lambda expressions

Posted 08 December 2014 - 05:14 PM

i see.. thanks.
for example: what SQLite.SQLiteConnection.Table<T> () <IL 0x00001, 0x0004f> tells me about the SQLite code error position?can i somehow translate 0x0004f to line of code?
Was This Post Helpful? 0
  • +
  • -

#6 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7056
  • View blog
  • Posts: 23,992
  • Joined: 05-May 12

Re: join tables in LINQ with Lambda expressions

Posted 08 December 2014 - 05:21 PM

Why are you getting to do a static code analysis when you have live code that you could run and debug? Just build SQLite with full debug options and use that assembly in your code. Run your code in the debugger and let the exception happen. You can then look up the callstack in the debugger and see exactly where that stack frame lives in the source code.
Was This Post Helpful? 0
  • +
  • -

#7 cdrosos   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 20-August 14

Re: join tables in LINQ with Lambda expressions

Posted 08 December 2014 - 05:29 PM

sorry if i sound so noob, it's because i am :). i will try to do what you say
Was This Post Helpful? 0
  • +
  • -

#8 cdrosos   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 20-August 14

Re: join tables in LINQ with Lambda expressions

Posted 08 December 2014 - 05:47 PM

I have delete the dll, throw the source, compile. The new stacktrace is this:
[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\DNDMusic\DNDMusic\Helpers\SQLite.cs:1755
[mono-rt]   at SQLite.SQLiteConnection.GetMapping (System.Type,SQLite.CreateFlags) [0x00036] in c:\Users\Exoskeletor\Documents\Projects\DNDMusic\DNDMusic\Helpers\SQLite.cs:304
[mono-rt]   at SQLite.TableQuery`1..ctor (SQLite.SQLiteConnection) [0x00010] in c:\Users\Exoskeletor\Documents\Projects\DNDMusic\DNDMusic\Helpers\SQLite.cs:2473
[mono-rt]   at SQLite.SQLiteConnection.Table<T> () [0x00001] in c:\Users\Exoskeletor\Documents\Projects\DNDMusic\DNDMusic\Helpers\SQLite.cs:768

Was This Post Helpful? 0
  • +
  • -

#9 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7056
  • View blog
  • Posts: 23,992
  • Joined: 05-May 12

Re: join tables in LINQ with Lambda expressions

Posted 08 December 2014 - 09:10 PM

Perhaps, I am missing something, but doesn't mono have an IDE? Run your code in the IDE abd it should take you right to the lounge of code that caused the crash, or as close as possible. It seems like you are just compiling your code and not running it in the debugger, and so you are asking us to debug by proxy.

I'm sorry if that sounds really naive, but I've never had to do any work in mono, so I'm not very familiar with the environment. I've been told that mono had has the full debugging facilities as if you were on Windows .NET with the CLR debugger (not necessarily the Visual Studio debugger), but perhaps I've just been told a bunch of hype. I used to use the CLR debugger and it was quite sufficient for basic needs.
Was This Post Helpful? 0
  • +
  • -

#10 cdrosos   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 20-August 14

Re: join tables in LINQ with Lambda expressions

Posted 09 December 2014 - 01:10 PM

thanks, i will try to debug it but i guess it would point me to the same lines of code.
The query seems to be valid, isn't that right? maybe there is a bug or something else, it's a pretty simple join, it should work
Was This Post Helpful? 0
  • +
  • -

#11 cdrosos   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 20-August 14

Re: join tables in LINQ with Lambda expressions

Posted 10 December 2014 - 03:41 PM

FOUND IT:
it seems that indeed SQLite does not support Join with LinQ. i used the query method and everything works now:

Name

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.ConvertAll(x => new Playlist { Name = x.Name, ResId = x.ResId, Tension = x.Tension });

Was This Post Helpful? 0
  • +
  • -

#12 cdrosos   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 20-August 14

Re: join tables in LINQ with Lambda expressions

Posted 26 March 2015 - 01:01 PM

May i ask something else?
How i can do something like this:
var q = database.Query<ListDetails> (
	"Select LD.Id, LID.ResId AS ResId, LD.Name from ListDetails LD"
	+ " inner join ListResID LID"
	+ " on LID.ListId = LD.Id where LID.ListId = ?",
	ListID).ToList ();
	return q.ConvertAll (x => new TrackList { 
		Name = x.Name, 
	        ResId = LID.ResId });


On the above code LID.ResId gives error of course. I want to return fields that doesn't belong on ListDetails but on ListResID. ConvertAll doesn't let me add ListResID i think, what i have to do?
Thanks guys

This post has been edited by cdrosos: 26 March 2015 - 01:04 PM

Was This Post Helpful? 0
  • +
  • -

#13 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 466
  • View blog
  • Posts: 2,212
  • Joined: 07-April 08

Re: join tables in LINQ with Lambda expressions

Posted 27 March 2015 - 11:21 AM

I'm not sure I understand. X represents the query within your database.Query<>(). Why can't you just do x.ResId?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1