Wrong SQL statements being generated when using System.Data.SQLite.Linq

Question

Has anybody successfully used System.Data.SQLite.Linq in their project?

Even after including this DLL in my project reference, it is apparent that Linq is calling the wrong provider when building the appropriate SQL statements. It is throwing an exception in SQLiteCommand with the following statement:

INSERT INTO [Inbox]
    ([Sender], [Subject], [Body], [Date], [ConversationID], [RemoteID], [ReplyTo])
VALUES
    (@p0, @p1, @p2, @p3, @p4, @p5, @p6)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]

The SCOPE_IDENTITY is not valid in SQLite. How do I direct System.Data.SQLite.DLL to use the SQLite Linq SQL builder when constructing SQL statements?

My DbProviderFactories is:

  <DbProviderFactories>
    <remove invariant="System.Data.SQLite"/>
    <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"/>
  </DbProviderFactories>

is this correct?

Additional info. I don't see System.Data.SQLite.Linq.dll in the list of Modules in VS2012 when my application is loaded even though it is in the bin folder. This confirms my suspicion that I'm missing something that explicitly references it but I can't figure out what it is.

Thanks!

I'm using SQLiteConnection: string connectionString = @"Data Source=" + databasePath + ";provider=System.Data.SQLite"; SQLiteConnection db = new SQLiteConnection(Config.ConnectionString); using (DataContext dc = new DataContext(db)) { // Code goes here; } — Steven Palmer, Sep 07 '13 at 20:18

score 2 · Answer 1 · answered Sep 08 '13 at 09:31

Based on further research, it appears that:

System.Data.SQLite.Linq.dll cannot be used by DataContext based classes. It may be possible to use it with ObjectContext. I'm investigating. Would be nice if the SQLite.Data folks would clarify this but I'm following up separately.
DbLinq is an alternative but when I tried it, its handling of Blob data seemed broken. Specifically it appears to use a dictionary for blob items and store every single byte in the dictionary as a value with the offset as a key. That was horribly slow. Also, it crashed when updating a blob with a new value that was larger than the old one.
Linq2Sql doesn't seem to be maintained and I wasn't able to get it to work at all due to some obscure error. Possibly my requirements are a bit more than its capabilities.

Ideally ObjectContext will work. Failing that, I'll drop Linq and fall back on direct SQLite access instead. I'd still welcome insight from anybody else who has managed to get Linq to work with SQLite though. Mine is an open source project so I'd prefer not to use a commercial provider.

score 1 · Answer 2 · edited May 23 '17 at 10:25

I've found System.Data.SQLite.Linq to be quite buggy.

Using the log method of the DataContext helps a lot in viewing the generated SQL as per this answer https://stackoverflow.com/a/8729736/74585

StringBuilder logBuilder = new StringBuilder();
db.Log = new StringWriter(logBuilder);
string sql = logBuilder.ToString();

For example the linq method .First() will generate SQL with SELECT TOP (1) ... but TOP (1) isn't supported in Sqlite. I've had to discover a lot of bugs like this by trail and error.

SQLite error when accessing first element in table using LINQ

Wrong SQL statements being generated when using System.Data.SQLite.Linq

2 Answers2