CODEWITHSUNDEEP
c#sqlitewin-universal-app
Bayern
Bayern

Reputation: 350

Is there a way to combine (JOIN) data from 2 tables together with SQLite?

I tried a few things to combine 2 tables, but the results returns only the data from the first table.

This are my tables:

 [Table("Person")]
public class Person
{
    [PrimaryKey, AutoIncrement]
    public int PersonID { get; set; }
    public string Name { get; set; }
    public int CompanyID { get; set; }
}

 [Table("Company")]
public class Company
{
    [PrimaryKey, AutoIncrement]
    public int CompanyID { get; set; }
    public string Name { get; set; }
}

And this is my query:

 var result = await _Connection.QueryAsync<Person>("SELECT * FROM Person JOIN Company ON Person.CompanyID = Company.CompanyID");

        return result;

But it only gives the properties from the first table "Person". What did i forgot?

Upvotes: 0

Views: 364

Answers (1)

Toddams
Toddams

Reputation: 1499

You forgot to use Present tense after did ;)

But actually this - _Connection.QueryAsync<Person> will return only Person fields because you specified a concrete Person type as a generic.

Create a 3rd class which has both Person and Company fields or use an anonymous type, then use something like this:

var query = from person in await db.Table<Person>().ToListAsync()
    join company in await db.Table<Company>().ToListAsync()
    on person.CompanyID = company.CompanyID
    select new 
    {
        Name = person.Name,
        CompanyName = company.Name
        // And so on
    }

Note: this will load all records to the memory because of ToListAsync()

Update: Instead of anonynous type, you can change your select to this:

PersonCompany personCompany = from person in .....
//---cut
on person.CompanyID = company.CompanyID
        select new PersonCompany
        {
            Name = person.Name,
            CompanyName = company.Name
            // And so on
    }

Upvotes: 1

Related Questions