System.Data.SQLite

anonymous added on 2014-05-27 22:22:37: (text/x-fossil-plain)

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?

mistachkin added on 2014-05-28 00:51:38: (text/x-fossil-plain)

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

mistachkin added on 2014-05-28 01:48:18: (text/x-fossil-plain)

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);

anonymous added on 2014-05-28 12:43:06: (text/x-fossil-plain)

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.

mistachkin added on 2014-05-30 19:40:59: (text/x-fossil-plain)

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}...

mistachkin added on 2014-05-30 20:07:04: (text/x-fossil-plain)

Working on a fix for release 1.0.93.0.

mistachkin added on 2014-05-30 21:18:46: (text/x-fossil-plain)

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