SQLite exception: "no such function: BigCount" when using "count" calling OData service with EntityFramework provider

Question

I use EF5 over SQLite database (using System.Data.SQLite 1.0.90.0). The entities are exposed via OData service

public sealed class MyService : DataService<MyEntities>

When I query my entities from inside my app it works ok, for example

using (var ents = new MyEntities) 
{
    var count = ents.SomeEntity.Select(ent => ent).Count();
}

When I send a request from browser like this

http://localhost:8737/MyService/SomeEntity

it also works fine, it returns me the list of my entities.

But when I create a following request

http://localhost:8737/MyService/SomeEntity/$count

OR

I query the service by service reference from some client app (and my query contains Count()), I get an Exception

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SQLite.SQLiteException: SQL logic error or missing database
no such function: BigCount

I suppose that when the SQL request is generated it contains aggregate function BIGCOUNT which SQLite doesn't have. If I change my database provider to SQL Server, then everything is absolutely fine. I don't know what I can do to change the way the request is generated. I tried to switch to Entity Framework 6 + System.Data.SQLite 1.0.94.0 but it's no use. I tried to stick to EF5 and change the versions of System.Data.SQLite to previous ones but nothing changed. The only difference I had was that my earlier problem was "solved" (in quotes because I would not call something I don't understand a solution) when I used the EF6+SQLITE1.0.94.0.

UPDATE 23/12/2014

We solved this problem by examining the System.Data.SQLite sources, finding the place where the "bigcount" keyword was incorrectly used, fixing it for our needs and then rebuilding the library.

As stated here the BigCount should be compiled to COUNT() in all databases except SQL Server. It looks like BigCount was compiled just to BigCount, or sth like that.

Rebuilding the library turned to be tricky itself, and since I'm only a little Junior yet, so my Team Lead did that part and I can't tell the details, which I didn't have time to dive deeper in. At least, it's a direction you can use to solve the same problem.

Answer 1

I also encountered the error SQL logic error or missing database\r\nno such function: BigCount and here are the detailed step-by-step instructions I used to update the code to resolve the issue on a Windows operating system:

1. Download fossil and extract fossil.exe to your <working> directory

2. Open a normal command prompt

3. Run cd <working>

4. Run fossil clone https://system.data.sqlite.org/ sds.fossil

5. Run fossil open sds.fossil

6. Run fossil update <release-tag>

   For example, fossil update release-1.0.105.2

7. Update .\System.Data.SQLite.Linq\SQL Generation\SqlGenerator.cs:
   a. un-comment lines 1978 - 1983
   b. replace line 1982 with the following line:

   aggregateResult.Append("COUNT");

8. Run cd Setup

9. Run set_YYYY.bat

   For example, to build the net451 binaries, run set_2013.bat

10. Run build.bat ReleaseManagedOnly

11. Remove the references to System.Data.SQLite.Linq and System.Data.SQLite.EF6 from the ASP.NET Web project

12. Add references to the new System.Data.SQLite.Linq.dll and System.Data.SQLite.EF6.dll from <working>\bin\2013\Release\bin in the ASP.NET Web project

Sources:

System.Data.SQLite Source Code
System.Data.SQLite Build Procedures
System.Data.SQLite Ticket UUID 76c2eaadc0297696b2c5fb10d41a22325f56f9b9