How do a join in SQLITE c#

Issue

How do I get data with a join of two tables? This is my code but only I get data of Classes but also I want the attributes of Active

var q = db.Query<Classes>(
            "select * from Active a"
          + " inner join Classes c"
          + " on c.idAc = a._id").ToList();

return q.Select(x => new Classes { Id= x.Id, name = x.name})
        .ToList();

I want to get the attributes of Active table as well, this Id= x.Id, name = x.name belongs to Classes table

Solution

For example, you want to get Id, name attributes from table Classes, and get detail attributes from table Active, so you should create new class tabledetail to contains these attributes.

public class tabledetail
{
    public int Id { get; set; }
    public string name { get; set; }
    public string detail { get; set; }
}

var q = db.Query<tabledetail>(
        "select c.Id,c.name,a.detail from Active a"
    + " inner join Classes c"
    + " on c.idAc = a._id").ToList();
        return q.Select(x => new tabledetail
        { Id = x.Id, name = x.name ,detail=x.detail }).ToList();

Here is the same thread that you can take a look:

https://forums.xamarin.com/discussion/36809/joining-tables-in-sqlite-net-and-returning-object-with-fields-from-both-tables

Answered By - Cherry Bu - MSFT