System.Data.SQLite

Well, I had nothing better to do this weekend, so I started experimenting with wiring a SQLite db up with LINQ, and much to my surprise, after reading some of the posts on these forums, it works.  At least as far as I've gotten.

Even though I had to set up the tables and columns manually using the designer, I was able to connect the data context to the database and execute single and multiple row queries.

I'm impressed.

Now, on to inserts, deletes, updates and such to see what else might work, and what kinds of issues I might find.

Thanks Robert.

Brad

Does anyone know of an example of overriding the LINQ insert method using a custom SQL statement, instead of a stored proc?  I've found many examples of the latter, but absolutely none of the former.  I've looked on various MS blogs, MSDN, CodeProject and Google.  Several articles on Scott Guthrie's blog mention the ability to override this method, along with the update and delete method, but he only provides an example of overriding the delete method.

When I insert using LINQ, the insert itself happens just fine.  I can see the new record in the DB file.  The problem I'm having is that the in memory object that holds the new row is not being updated with the new primary key.  As far as I can tell by looking at the SQL code generated by LINQ by default, I'm doing all the right things, i.e. returning the new key from the query used to insert, but I'm apparently missing some essential step that accomplishes the update to the in memory version.

Brad

Would you mind sharing how you managed to connect the DataContext to a SQLite database? I've been unable to figure that out so far.

Thanks,

Doug Harber

I don't have the actual code here with me now, so this if from memory.  Basically, one of the constructor overrides takes a DbConnection as a parameter.  I used that one.

SQLiteConnection connection = new SQLiteConnection(connectionString);
MyDataContext db = new MyDataContext(connection);

You have to implement the partial methods for inserting and updating rows, because the default query generator generates code that is compatible with MS SQL Server specific, and which is not compatible with SQLite.  However, a simple select appears to work without override.

When I get home, I'll post some of the code I've got that actually works.

Brad

I've been corresponding with a couple of Microsoft guys about this, and they are saying that it likely won't work until a full blown LINQ provider is available for SQLite, or LINQ to Entities is release.

For the moment, I don't believe this presents too much hardship, since the only thing that I've found so far that doesn't work is the automatic refresh of DB generated data, such as primary keys and timestamps, in the in-memory object that is used to do an insert or an update.  The actual insert or update works fine in the DB itself, and subsequent queries of that data retrieve the new information correctly.

I believe that it will be possible to isolate this particular lack and work around it in most cases until such time as it can be made to work.  If I can't do that, I'm a crappy programmer and should give it up anyway. :)