Overall, using sqlite under Windows 8 has been a neat experience. Stuff seems to work, which is nice.
There are a couple of things that were a surprise to me though – the last of them being that FirstOrDefault() does not behave as it should (or at least as I would have expected it to) in sqlite-net.
If you use LINQ for accessing .NET collections, by and large, calling FirstOrDefault() (or First()) with a lambda expression is a very convenient way of finding elements inside them.
The magic though happens when you make a call to FirstOrDefault(lambda) on a LINQ collection that supports IQueryable – these guys have a chance of further optimizing your call.
For example. If you have a List<Person> and you want to find the first person whose Id is equal to 10, you can run the following: list.FirstOrDefault(x => x.Id == 10). When used on a collection, this is just like doing a foreach and looking for the relevant item through the entire collection. There’s no magic happening here other than the berevity of the syntax.
However, if you use a LINQ SQL Server connection and make the same call off the People table, for example (connection.People.FirstOrDefault(x => x.Id == 10)), some magic does happen. The library will translate your lambda (and the call itself) into a SQL statement that will execute against the engine. That means that if the People table is indexed by Id, the call will return the desired results very quickly – without actually having your code run over all the items. Similarly, when using Where(), the database engine is the entity that actually returns the relevant results – not your code actually iterating over the entire table.
Sqlite-net is a set of wrappers that lets you use sqlite through LINQ and LINQ-like mechanisms. The implementation is far from complete and is missing a lot of bits (joins are not supported through LINQ for example).
However, the thing that got me was the fact that FirstOrDefault() behaves very differently from Where().
With Where(), sqlite-net will behave as expected – crafting a WHERE statement behind the scenes (though sadly you don’t get access to it) and executing it against the sqlite engine. The same does not happen with FirstOrDefault() though – instead of creating a SQL statement, the library actually realizes the entire table (bringing it in it’s entirety into memory) and actually runs the lambda on each element.
For small tables, this is really not a big deal – sqlite is fairly fast. However, for big tables, this can be pretty bad. In my tests there’s a 100x cost of using the “wrong” way:
18ms: table.Where(x => x.Id == 30000).FirstOrDefault()
2200ms: table.FirstOrDefault(x => x.Id == 30000);
Be careful when using 3rd party libraries that are not fully developed (duh!). And specifically, in sqlite-net, do not call FirstOrDefault() to get at a specific record in a table. Use Where() instead.