Geek asks and answers

SQLite Join Windows 8 Metro C # with sqlite-net

I am using C # and SQLite for the database for the Windows-8-Metro-App. I want to use Join-Command, but don’t know how to read data back. This will not work:

db.Query<Person>("SELECT * FROM Person, Job WHERE Person.JobID = Job.ID");

And this is not implemented:

db.Query<Person, Job>("SELECT * FROM Person, Job WHERE Person.JobID = Job.ID");

Does anyone have an idea how to do this?

+5
source share
2 answers

The connection is fine if it's out of date - you should use the new syntax

 SELECT * FROM Person INNER JOIN Job ON Person.JobID = Job.ID

Your problem is that you are returning - you are returning Person and Job data, so you need to create a class that matches the data structure that you are returning, or return only the person or job.

 db.Query<Person>("SELECT Person.* FROM Person INNER JOIN Job ON Person.JobID = Job.ID");           
 db.Query<Job>("SELECT Job.* FROM Person INNER JOIN Job ON Person.JobID = Job.ID");
+7
source

I was able to use it correctly, you can try.

 var db = new SQLiteAsyncConnection(_dbPath);
 var query = from person in await db.Table<Person>().ToListAsync()
                    join job in await db.Table<Job>().ToListAsync()
                    on person.JobID = job.ID
                    select new
                    {
                        Name=person.Name
                    };
0
source

More articles:


All Articles