System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 4ca56c780c92f6e308abf1ad5bb76be2a3e29a68
Title: AsyncTableQuery "Where" clause can't handle "OR" (||).
Status: Closed Type: Incident
Severity: Minor Priority: Medium
Subsystem: LINQ Resolution: Not_A_Bug
Last Modified: 2016-08-31 05:00:24
Version Found In: v3.1.1
User Comments:
anonymous added on 2016-08-16 09:04:24:
Table has data with ID 1. 

var filteredIDs = new List<int>{1};

_connection.Table<T>().Where(x=>filteredIDs.Contains(x.ID)).ToListAsync()
Result has 1 row returned. 

_connection.Table<T>().Where(x=>filteredIDs.Count==0||filteredIDs.Contains(x.ID)).ToListAsync()
Result has 0 row returned.

mistachkin added on 2016-08-25 17:10:09:
Just tested with Visual Studio 2012 and EF6.  Does not reproduce.

mistachkin added on 2016-08-25 17:17:29:

Do you know what SQL queries get generated for these statements? The SQL generated here locally looks correct:

      private static int ToListAsync()
      {
          using (northwindEFEntities db = new northwindEFEntities())
          {
              int result = 0;
              var ids = new List<int> { 10248 };

              Task<List<Orders>> task1 = db.Orders.Where(x => ids.Contains((int)x.OrderID)).ToListAsync();

              task1.Wait();
              result += task1.Result.Count;

              Task<List<Orders>> task2 = db.Orders.Where(x => ids.Count == 0 || ids.Contains((int)x.OrderID)).ToListAsync();

              task2.Wait();
              result += task2.Result.Count;

              return result;
          }
      }
SQLite trace: Preparing {SELECT 
1 AS [C1], 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[OrderDate] AS [OrderDate], 
[Extent1].[RequiredDate] AS [RequiredDate], 
[Extent1].[ShippedDate] AS [ShippedDate], 
[Extent1].[Freight] AS [Freight], 
[Extent1].[ShipName] AS [ShipName], 
[Extent1].[ShipAddress] AS [ShipAddress], 
[Extent1].[ShipCity] AS [ShipCity], 
[Extent1].[ShipRegion] AS [ShipRegion], 
[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
[Extent1].[ShipCountry] AS [ShipCountry], 
[Extent1].[CustomerID] AS [CustomerID]
FROM [Orders] AS [Extent1]
WHERE 10248 = ([Extent1].[OrderID])}...
SQLite trace: Preparing {SELECT 
1 AS [C1], 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[OrderDate] AS [OrderDate], 
[Extent1].[RequiredDate] AS [RequiredDate], 
[Extent1].[ShippedDate] AS [ShippedDate], 
[Extent1].[Freight] AS [Freight], 
[Extent1].[ShipName] AS [ShipName], 
[Extent1].[ShipAddress] AS [ShipAddress], 
[Extent1].[ShipCity] AS [ShipCity], 
[Extent1].[ShipRegion] AS [ShipRegion], 
[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
[Extent1].[ShipCountry] AS [ShipCountry], 
[Extent1].[CustomerID] AS [CustomerID]
FROM [Orders] AS [Extent1]
WHERE (0 = @p__linq__0) OR (10248 = ([Extent1].[OrderID]))}...


mistachkin added on 2016-08-25 17:19:59:
Another note, the following using statements were needed in the testef6 project:

using System.Threading.Tasks;
using System.Data.Entity;

anonymous added on 2016-08-30 13:24:12:
Sorry I got confused with Sqlite site. This is a SQLite.NET issue, not System.Data.SQLite issue. 

I've already sent email to the SQLite.NET bug tracking email address, sqlite-users@mailinglists.sqlite.org and waiting for answers. 

My environment is VS2015 + Xamarin + SQLite.NET v3.1.1
Function I'm calling which is having the described issue is 
AsyncTableQuery<T> SQLiteAsyncConnection.Table<T>()