Sqlite PCL and Linq - SQLite.Net Table method loads an entire table into a collection?

Question

I am building an application for Windows Phone 8. I decided to use Sqlite PCL in the portable library to cache some data.

I did not find the latest information on the Internet, can I use linq in the table or not.

Of course i can do it

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

If I look at the return value of the Where statement, this is TableQuery .

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

In my opinion, I said that Linq filters the elements directly in the sql statement, since the Where function returns a TableQuery , but I have not yet found confirmation.

Second question: is it the same when I use FirstOrDefault ?

Answer 1

This is apparently not so obvious. I just found a very interesting forum here

To summarize if you do this

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

It is very different from this.

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

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

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

However, the second request:

 select * from "AlertType" 

As mentioned

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

This means that Linq will modify the sql statement accordingly.

"But SQLite.TableQuery has only FirstOrDefault, which does not accept Parameters:"

This means that if you use FirstOrDefault with a predicate, it will retrieve the entire table, but if you use it without a predicate in the table, this will change 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 FirstOrDefault for the collection in memory."