Joining tables in SQLite.Net and returning object with fields from both tables

ChristopherDrososChristopherDrosos GRMember ✭✭
edited March 2015 in Xamarin.Android

Hello guys, i can join 2 tables 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.ConvertAll(x => new Playlist { Name = x.Name, ResId = x.ResId, Tension = x.Tension });

but is it possible to do something like this? (this code of course is not working)

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

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?

Posts

  • JoeMankeJoeManke USMember ✭✭✭✭✭

    What happens with Query<T> is it performs the query and tries to construct items of type T. It's going to break trying to assign the values from the second table to an object without those properties. You should use database.Query<TrackList>() rather than selecting ListDetails then trying to convert it to a TrackList.

  • ChristopherDrososChristopherDrosos GRMember ✭✭

    indeed thanks,
    i thought on the query i have to put one of the tables from the join.

Sign In or Register to comment.