System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 1a933121135422798cee85c85378bb071f410eb9
Title: The Any() query fails But Contains() query runs well. What is the Problem?
Status: Deferred Type: Feature_Request
Severity: Important Priority: Medium
Subsystem: LINQ Resolution: Need_More_Info
Last Modified: 2016-06-21 20:21:32
Version Found In: 1.0.98
User Comments:
anonymous added on 2016-02-03 12:32:58:
Tools being used:

Entity Framework 6.0
System.Data.Sqlite 1.0.98 Package [Latest Stable Build]
Problem: Suppose the Code block given Below:

using(var dbContext = new DatabaseContext())
{
var hugeDataListObj= dbContext.Table1.Select(x=>x.Field1).ToList();
//Do other Stuff here with hugeDataListObj.

// The Below query will fail for Parser Stack overflow.
var requiredResultObj = dbContext.Table2.Where(x=>hugeDataList.Any(y=> y==x.Field1)).ToList(); 


//but The code given below works out well.
var requiredResultObj = dbContext.Table2.Where(x=>hugeDataList.Contains(x.Field1)).ToList(); 

/* Below given is the Detailed Exception.*/
 "InnerException": {
            "ExceptionType": "SQLiteException",
            "ExceptionMessage": "SQL logic error or missing database\r\nparser stack overflow",
            "StackTrace": "   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)\r\n   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()\r\n   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)\r\n   at System.Data.SQLite.SQLiteDataReader.NextResult()\r\n   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)\r\n   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)\r\n   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)\r\n   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)",
            "Data": {},
            "ErrorCode": "-2147467259",
            "Source": "System.Data.SQLite",
            "HelpLink": null,
            "InnerException": null
        }
}
I have looked at the Query of Any and Contains. Both have a Huge difference. But then What i am confused about is that is Any() has got a Performance issue ? Or is this is Case Specific?

mistachkin added on 2016-02-03 18:58:47:
Can you share the schema of the database tables involved?

mistachkin added on 2016-02-03 20:53:54:
I've managed to reproduce the issue here.  It appears that the Entity Framework
is causing a query to be generated that includes extremely deeply nested SELECT statements.

mistachkin added on 2016-02-03 21:39:17:
I'm not sure this can be fixed without changing quite a lot of the LINQ
integration code used by System.Data.SQLite and possibly breaking backwards
compatibility in the process.

mistachkin added on 2016-03-31 00:33:52:
May be related to VisitExpressionEnsureSqlStatement.

mistachkin added on 2016-04-01 02:06:04:
Fixing this will require significant changes to the System.Data.SQLite.Linq code.
Setting this ticket to be 'deferred' to a later date.