So I noticed that one of the accepted proposals for the Mono project is to create a LINQ provider for SQLite. Major props to this (its something I totally want to see!) and I’m glad to see that LINQ in Mono is going to be its own beast, I love it when the FOSS community just takes a technology and runs with it! Anyways, I wanted to try and 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 learned.
First, people really want this, and there are several half-complete implementations floating around, including mine (read only, no commit/update/delete support) and this one.
Second, support for just queries is quite easy. Support for complete CRUD, tedious but not to difficult (lots of examples already exist). Support for the generation/mapping/reflection of a database to real Linq objects, this is the tricky part (specifically the UI elements when unable to just piggyback the Visual Studio work).
Anyways, all the luck in the world to this GSOC project, I would really like to see a working implementation come from this!
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).
Linq To Sqlite Download
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. 
Recent Comments