Sqlite PCL and Linq - Is SQLite.Net Table method loading the entire table into a collection?

Question

I'm a building a windows phone 8 application. I've decided to use Sqlite PCL in a portable library to cache some data.

I didn't find recent information on the internet whether I can use or not linq on a table.

Of course I can do that

var phones = db.Table<PhoneNumber>().Where(x => some condition).ToList();

If I take a look at the return value of the Where statement, it is a TableQuery.

My question is : Do I retrieve all the phone numbers by doing this and then use Linq to filter the items? Or does linq filter the items directly in the sql command before returning them?

To my mind, I wound say that Linq filters the items directly in the sql statement as the Where function return a TableQuery but I didn't find any confirmation yet.

2nd question: Is it the same when I use FirstOrDefault?

Answer 1

Apparently, it's not that obvious. I've just found a very interesting forum post here

To summarize, if you do this

var whereFirstOrDefault = Table<AlertType>().Where(a => a.Name.Equals(alertType.Name)).FirstOrDefault();

It is very different than doing this

var firstOrDefault = table.FirstOrDefault(a=> a.Name.Equals(alertType.Name));

The first query generates this command and does not retrieve the whole table:

select * from "AlertType" where ("Name" = (?)) limit 1

However, the second query is :

select * from "AlertType"

As mentionned,

"SQLite.TableQuery has an extension method for 'Where' which takes a predicate."

That means that Linq will modify the sql statement in consequence.

"But SQLite.TableQuery only has a FirstOrDefault that doesn't take parameters:"

It means that, if you use FirstOrDefault with a predicate, it will retrieve the whole table but if you use it without a predicate on a tablequery, it will modify the sql statement

"If you call FirstOrDefault with a predicate (my second approach), SQLite.NET selects the entire table and then uses LINQ To Objects to do the FirstOrDefault on the collection in memory."