System.Data.SQLite

I have the same query written in two different styles - one returns the right result, the other returned 0.

// query 1 - passed
var c = (
   from i in db.Orders
   where i.Date == date
   select i
).Count();
Assert.AreEqual(2, c);

// query 2 - failed, return 0.
var c = db.Orders.Where(i => i.Date == date).Count();
Assert.AreEqual(2, c);

Is the second style not supported?

Is this using the Northwind sample database?  What is the type and value of the
"date" variable?

So far, I'm unable to reproduce this issue locally.  This is what I tried:

DateTime? date = new DateTime(1997, 1, 1, 0, 0, 0, DateTimeKind.Local);

var c1 = (from i in db.Orders where i.OrderDate == date select i).Count();
var c2 = db.Orders.Where(i => i.OrderDate == date).Count();

Console.WriteLine("c1 = {0}", c1);
Console.WriteLine("c2 = {0}", c2);

It's not from a well known DB.  I created the table myself.  The definition is very simple:

CREATE TABLE "Order" ("Id" bigint PRIMARY KEY  NOT NULL , "Date" DATETIME NOT NULL , "Type" varchar(8) NOT NULL )

I ran some more tests with how the date is created.  I found that how the date is created (in place or as a variable) instead of the syntax style caused the behavior difference.

// query 1 - passed, returned 2
var c = db.Orders.Where(i => i.Date == new DateTime(2014, 5, 23)).Count();

// query 2 - failed, returned 0
var date = new DateTime(2014, 5, 23);
var c = db.Orders.Where(i => i.Date == date).Count();

Same behavior for both styles and both DATE and DATETIME sql type.

The inline date version produces this trace output:

  SQLite trace: Preparing {SELECT
  [GroupBy1].[A1] AS [C1]
  FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
  LEFT OUTER JOIN  (SELECT
  	Count([Filter1].[A1]) AS [A1]
  	FROM ( SELECT
  		1 AS [A1]
  		FROM [Orders] AS [Extent1]
  		WHERE '1997-01-01 00:00:00.000' = [Extent1].[OrderDate]
  	)  AS [Filter1] ) AS [GroupBy1] ON 1 = 1}...
  SQLite trace: Statement 68662496 paramter count is 0.

The variable date version produces this trace output:

  SQLite trace: Preparing {SELECT
  [Project1].[C1] AS [C1]
  FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
  LEFT OUTER JOIN  (SELECT
  	[GroupBy1].[A1] AS [C1]
  	FROM ( SELECT
  		Count([Filter1].[A1]) AS [A1]
  		FROM ( SELECT
  			1 AS [A1]
  			FROM [Orders] AS [Extent1]
  			WHERE [Extent1].[OrderDate] = (@p__linq__2)
  		)  AS [Filter1]
  	)  AS [GroupBy1] ) AS [Project1] ON 1 = 1}...
  SQLite trace: Statement 68714872 paramter count is 1.
  SQLite trace: Statement 68714872 paramter #1 name is {@p__linq__2}.
  SQLite trace: Binding statement 68714872 paramter #1 with database type DateTime and raw value {1/1/1997 12:00:00 AM}...
  SQLite trace: Binding statement 68714872 paramter #1 as type System.DateTime with value {1997-01-01T00:00:00-08:00 Local 629876736000000000}...
  SQLite trace: Binding statement 68714872 paramter #1 as type System.Byte[] with value {313939372d30312d30312030303a30303a303000}...

Working on a fix for release 1.0.93.0.

Fixed on trunk via check-in [4dc462af32].