System.Data.SQLite

For very simple queries, I get errors like the following: 

Message:  "SQLite error\r\nnear \".\": syntax error"

Stack Trace:

   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)

   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()

  at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)

  at System.Data.SQLite.SQLiteDataReader.NextResult()

  at ...

For example, my queries look like:

var result = (from f in this.AllFiles where f.FilePath.Equals(filePath) select f).First();

Or:

var result = (from f in this.AllFiles where f.FilePath.Equals(filePath) select f).FirstOrDefault();

This is using System.Data.SQLite version 1.0.66.0

Also, I am getting "SQLite error\r\nnear \"SELECT\": syntax error" when trying to use the .InsertOnUpdate(data); .SubmitChanges(); pattern, being thrown at the call to SubmitChanges.  (Did this stuff work before in another version, will it work soon, or did I just waste a ton of time simplifying ugly SQLite commands code to use Linq?)

Ok fortunately I stumbled onto this post wherein a side comment points out that DataContext is supposedly not the thing to use.

Being a newbie to EF (and semi-newbie to Linq and such in general), apparently some of my assumptions about EF were completely wrong.  I thought the beautifully simple process that I went through earlier with Linq to SQL was what EF was about;  zero fuss with the DB, zero mapping steps, zero external tools, etc...  Here was the process for Linq to SQL:  I made a C# class that looks like my table like:

public class MyDatum { 

[Column] public int Foo {get;set;}  

[Column] public long Bar {get;set;} }  

etc...  (Plus an ID column marked up as autoincrement, etc.)  Next define a DataContext (optionally as its own class but structure is up to you) like:

public class MyDataContext : DataContext {

public MyDataContext(string connection) : base(connection) { this.MyDataTable = this.GetTable<MyDatum>(); }

public Table<MyDatum> MyDataTable { get; private set; }

etc...  And viola!  Linq interaction to SQL in like 5 minutes of work...    var results = from MyDatum d in dataContext.MyDataTable select d;   WOOT!

 

So it is this level of ease with which I would love to work with SQLite.  I am not a DBA and the more I can shield myself from inadvertently becoming one, the better.  Further, I cannot insert installation dependencies for building the code; it seems to me the ADO.Net Entities Framework would be problematic (IE the process described here that everyone seems to land at, yet still has no sufficient instruction for that part).  The best compromise I have found so far is using DbLinq with the System.Data.SQLite.dll provided here.  Sadly, the autogenerated code needed a handful of changes so I could not easily regenerate it on the fly, most notably the longs in the db were exposed as string properties, and upon refactor->rename things to fix the capitalization that it butchered, needed to be followed up by renaming hard-coded magic strings.  But all in all, the rough fascimile of MyDataContext that it generated behaves enough like DataContext that I have my Linq back and can .InsertOnSubmit(); and .SubmitChanges();   WOOT!  Hopefully my DB layout doesn't change often though......

Still, maybe I'm missing something.  Maybe there's a way yet to generate the DataContext class against strongly typed classes more like the Linq to SQL way I described, rather than against (and including all the extra garbage I don't necessarily want to expose and work with in) the DB.  Thoughts?  Are there some good training posts out there that I've missed which can help fill in the gaps in my (mis)understandings and such?