SqlLite Linq Provider

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. šŸ˜¦


5 comments so far

  1. jeffreyabecker on

    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.

  2. […] 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 […]

  3. Jlove on

    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.

  4. JCollum on
  5. asava samuel on

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: