System.Data.SQLite

I try concatenating 2 queries:

var files1 = context.Files.Where(f => f.ParentFolder.SubFolders.Any()).OrderByDescending(f => f.Size).Take(10); var files2 = context.Files.Where(f => !f.ParentFolder.SubFolders.Any()).OrderByDescending(f => f.Size).Take(10); foreach(var file in files1.Concat(files2)) [...]

Throws SQLiteException: SQL logic error or missing database ORDER BY clause should come after UNION ALL not before

at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior) 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) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Since I'm only interrested of the 10 largest files of both groups I see no chance in reordering my query.

But I have to admit that for this query its not important that SQLite is doing the concatenation so in this case a fallback to IEnumerable works well: foreach(var file in files1.AsEnumerable().Concat(files2)) [...]

Blocking 1.0.95.0 until the root cause is known.

Do you have an example schema (and entity model) that demonstrates the issue?

This is also an issue with the LIMIT and OFFSET clauses.

I was able to reproduce the issue locally.

There is now a candidate fix for this issue on the tkt-0a32885109 branch:

  [/timeline?r=tkt-0a32885109]

Basically, the fix is to disallow the left SELECT statement of a compound
operator from having an ORDER BY, LIMIT, or OFFSET clause.

Please let us know if this clears up the issue for you.