How to return Wrapper obj of TableOjb1 and TableObj2 using linq

Question

class TableObj1 {
    public string Id {get; set;}
    public string Name {get; set;}
}

class TableObj2 {
    public string Id {get; set;}
    public string Email {get; set;}
}

class MergeObj {
    public TableObj1 Obj1 {get; set;}
    public TableObj2 Obj2 {get; set;}
}

My question is how to return a list of MergeObj when joining the two tables. I tried:

public IEnumerable<MergeObj> QueryJoin() {
    return (
        from obj1 in conn.Table<TableObj1>()
        join obj2 in conn.Table<TableObj2>()
        on obj1.Id
        equals obj2.Id
        select new MergeObj{Obj1 = obj1, Obj2 = obj2}
    );
}

void main() {
    IEnumerable<MergeObj> mergeObjs = QueryJoin();
}

But QueryJoin() gives Exception: System.NotSupportedException, Joins are not supported.

please note I'm using sqlite.net not ADO.net.

There really doesn't look like there's anything wrong with your linq. Do you get values from `conn.Table()` and `conn.Table()` without joining them? And if so, do they have matching `Id` properties? — DavidG, Oct 19 '14 at 18:56
I get values from conn.Table() and conn.Table(). They all match Id properties. — macio.Jun, Oct 19 '14 at 19:23
Does anything happen if you change 'mergeObjs = QueryJoin()' to 'mergeObjs = QueryJoin().ToList()'? — Stephen Kennedy, Oct 19 '14 at 20:31
Linq to database queries use deferred execution, the query is built up in an expression tree but is only executed when the data is enumerated (which ToList() will do). I'm possibly shooting blanks here however as you said the result is null and not empty. — Stephen Kennedy, Oct 19 '14 at 20:34
Thanks @Stephen Kennedy, I tried mergeObjs = QueryJoin().ToList(), still has some sqlite.net syntax error, somehow the join linq is not compilable. Sry I did not explain clearly, QueryJoin() gives syntax error, but xamarin studio is not giving any details for the syntax error tho. — macio.Jun, Oct 19 '14 at 23:51

Seb · Accepted Answer · 2014-10-20T19:46:49.050

3

Try doing the select after casting the join result to a list.

public IEnumerable<MergeObj> QueryJoin()
    {
        List<TableObj1> t1 = conn.Table<TableObj1>().ToList();
        List<TableObj2> t2 = conn.Table<TableObj2>().ToList();

        return t1.Join(t2, outer => outer.Id, 
                           inner => inner.Id, 
                           (outer, inner) => new MergeObj { Obj1 = outer, Obj2 = inner });
    }

Edit : Since your database don't seems to support join, you can extract the result of your database in two distinct List and then join them using LINQ.

edited Oct 20 '14 at 19:46

answered Oct 20 '14 at 15:56

Seb

1,230
11
19

Sorry @Seb, I still get exception: System.NotSupportedException, Joins are not supported. – macio.Jun Oct 20 '14 at 18:36
Ok I will modify my answer then. You will have to move the toList one level up – Seb Oct 20 '14 at 18:41
Hi @seq, your answer does solve the problem, but what if I want only return results with Id equalling 1? Then I need to do twice WHERE clauses for both t1 and t2 before I can do the Join... – macio.Jun Oct 20 '14 at 19:48
The join query I proposed in my answer is an INNER JOIN so technically you can do the pre-filtering on either t1 OR t2. BUT, if you want only results with Id equalling 1, you will benefict from doing the WHERE clause twice because less data will be fetched from your database – Seb Oct 20 '14 at 20:02
I agree. I accept your answer now. Xamarin Studio does not allow join using linq select to convert anonymous type to MergeObj, but only object list join. Thank you. – macio.Jun Oct 20 '14 at 20:08

score 1 · Answer 2 · answered Oct 20 '14 at 18:58

1

You might need to project to an anonymous type, then create your objects using Linq-to-Objects:

public IEnumerable<MergeObj> QueryJoin() {
    return (
        from obj1 in conn.Table<TableObj1>()
        join obj2 in conn.Table<TableObj2>()
        on obj1.Id
        equals obj2.Id
        select new {obj1, obj2}
    ).AsEnumerable()
     .Select(o => new MergeObj{Obj1 = o.obj1, Obj2 = o.obj2}) ;
}

answered Oct 20 '14 at 18:58

D Stanley

149,601
11
178
240

Hi @D Stanley, your answer is the same as seb's. Somehow using linq Select() can't convert anonymous type to MergeObj type. Seb's new answer does solve the problem, but I need do twice WHERE for both TableObj1 and TableObj2 before I can create objects using linq-to-objects if I have a WHERE clause in addition to the join clause. – macio.Jun Oct 20 '14 at 19:46
@macio.Jun No, our answers are different. Mine is doing the join _before_ switching to Linq-to-objects. What error are you getting? – D Stanley Oct 20 '14 at 19:50
same error: System.NotSupportedException, Joins are not supported – macio.Jun Oct 20 '14 at 19:52
@D Stanley, Seb's old answer is exactly the same as yours(first join then linq-to-objects), but gives exception above... – macio.Jun Oct 20 '14 at 19:53
@macio.Jun I was looking at his _current_ answer which is different. I see now that he originally had a similar answer. – D Stanley Oct 20 '14 at 19:55

How to return Wrapper obj of TableOjb1 and TableObj2 using linq

2 Answers2

Linked