SQLite error when accessing first element in table using LINQ

Question

I'm having the following issue when accessing a SQLite database using LINQ. Abreviated code looks like this:

...

using System.Linq
using System.Data.Linq
using System.Data.Linq.Mapping
using System.Data.SQLite

...

DataContext sqltContext= new DataContext(sqltConnection);
featureTable = sqltContext.GetTable<FeatureModel>();
count= featureTable.Count(); // count == 1

The following line fails with error: "SQL logic error or missing database\r\nunknown error"

//currentFeature = featureTable.First();

But iterator works fine:

foreach (var feature in featureTable)
{
  currentFeature = feature;
  break;
}

In both cases, I'm getting the following error in debug output:

SQLite error (1): near ".": syntax error

What would cause this error? I'm hoping someone has seen this issue before and can point me in the right direction. But I will probably end up stepping through the System.Data.SQLite code in the debugger at some point.

EDIT: based on comment below from GeorgeT, I added sqltContext.Log = Console.Out;. Based on the logging output, it appears that LINQ is generating a SELECT TOP command, which isn't compatible with SQLite. The solution to this would seem to be to avoid the First or FirstOrDefault methods, or else use something like DBLinq

See related question http://stackoverflow.com/questions/17280051/sqlite-error-near-s-syntax-error. — Mihai8, Apr 30 '14 at 14:26
This looks like an error in the generated SQL text. This related question's chosen answer links to an article that may help you see the text: http://stackoverflow.com/questions/2975554/linq-to-sql-with-sqlite-syntax-error-near-select-when-inserting?rq=1 — George T, Apr 30 '14 at 14:27

score 3 · Answer 1 · answered Apr 30 '14 at 15:31

Calling the .First() method generates a SQL query using the SELECT TOP command, which isn't compatible with SQLite. The solution is to avoid using the .First() method, or to use a library which supports LINQ queries to SQLITE, such as DbLinq or LinqConnect.

score 2 · Answer 2 · answered Dec 08 '14 at 08:38

As tdr's answer as mentions .First() generates incorrect SQL and can't be used. I've had to convert the results to a list and then use .First() on the list as a workaround. It won't be very efficient if the results are very large though:

var item = featureTable.ToList().First();

SQLite error when accessing first element in table using LINQ

2 Answers2

Linked