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 stackoverflow.com/questions/17280051/…. — Mihai8
– Mihai8, Commented Apr 30, 2014 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: stackoverflow.com/questions/2975554/… — George T
– George T, Commented Apr 30, 2014 at 14:27

tdr · Accepted Answer · 2014-04-30 15:31:31Z

3

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.

answered Apr 30, 2014 at 15:31

tdr

1731 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matthew Lock · Accepted Answer · 2014-12-08 08:38:00Z

2

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();

answered Dec 8, 2014 at 8:38

Matthew Lock

13.6k14 gold badges97 silver badges132 bronze badges

Collectives™ on Stack Overflow

SQLite error when accessing first element in table using LINQ

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related