Ok, so as many of you may have noticed in my last post I’ve taken a real interest in C# 3.0 and its new nifty features. Now, I’m mostly just excited for the simple collection manipulations, but the whole Linq to SQL thing is nagging in the back of my mind. Now while complete CRUD will no doubt take some serious code, simple query support is not that difficult to implement. After following mattwar’s blog series on a generic DB provider for Linq, I decided that I wanted that awesome glory against a lean and mean sqlite db.
So, about an hour or so of messing and meddling I have a few samples working. (The attached zip) Its a Visual Studio 2008 Beta 2 project (I’ll be writing autotools magic for mono later this week) so sorry for that, the code can still be imported into Monodevelop, just the solution/project files won’t work. Anyways, I have tested a smattering of JOIN’s and all sorts of simple selects without issue. However, the elements of sqlite that behave differently tend to do so silently and without complaint, making it harder to be certain that everything is working. However, I’m planning on fleshing out a set of test queries, however, for now I could really just use the help with testing/checking the SQL (as I’m no sqlite Guru).
Known Issues
- DataType sloppyness – I hope to handle this better (storing a DateTime string in TEXT would extract to DateTime successfully) right now you need to pretty much just use strings or numeric values.
- Inefficient Queries – Not being a Sql master, I can’t say that much of whats generated is the best way to do things, please, if you know then share!
- OrderBy issues – Its just hard as heck to get working, it seems to work fine sometimes, but no promises.
Anyways, play around, have fun, and note that you need the Sqlite provider for ADO.Net (duh).
I’m looking at db_linq, which is a full (bi-directional, change tracking, general awesome crazyness) solution, this is really just a way to query sqlite db. I might try to add a sqlite provider to db_linq at some point, its just that their system is very different from my implementation, so there wouldn’t be too much shared code. 
UPDATE: This post is obsolete, please see this post on Sqlite Linq Providers.
Ok, so I looked at linq to sql, dblinq & stuff and figured a few things out.
1) System.Data.Linq was intentionally broken. The extensibility interfaces where marked internal and this extensibility is officially not supported.
2) DB_Linq is basically a re-implementation of the System.Data.Linq namespace.
3) I do not like how DB_Linq is implemented. DB_Linq uses symbolic redefinition which points the classes at vendor specific implementations to abstract the database rather than a provider or factory model.
4) Getting the data out of the DB into entities will be fairly straight forward. System.Data.SQLite defines a pretty reasonable set of sqlite -> .net mappings. The .net -> sqlite mapping is _much_ harder.
[...] get in touch with the mentor/student of this project and share my experience (as the author of the current LINQ to SQLite component ). But contact info seemed hard to come by, so I thought I would post what I had [...]
What dont you like DB the linq db? I found that after reading the tutorials at linqhelp.com I was able to get the data out of the db pretty easy.
Your link http://kubasik.net/blog/wp-content/uploads/2007/10/linqtosqlite.zip is dead.
My apologies, I’ll get it back up in the next day or so.
I followed the non-deprecated instructions at (http://kubasik.net/blog/2009/02/09/using-linq-to-sqlite-providers-updated/) but am confused how to do teh CRUD operations on a table.
How to do?
Help!
Thanks.
I can not download linq 2 sqlite, very sorry to say that link is invalid. If the link is available, can send me e-mail?
mwc@foxmail.com (Server location: China)
Greatly appreciated!
China boy,BuJi LongGang GuangDong
[...] – If you are rockin’ the 3.0 framework there is even a SQLite Linq provider. [...]