How to return Wrapper obj 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 MergeObj list when joining 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 an exception: System.NotSupportedException, Joins are not supported.

Please note that I am using sqlite.net and not ADO.net.

Answer 1

Try making a selection after adding the join result to the 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 }); } 

Change Since your database does not seem to support a connection, you can extract the result of your database into two different lists and then join them using LINQ.

Answer 2

You may need to create an anonymous type and then create 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}) ; }