Linq - Filtering by date doesn't work

[eminem]

Hi

i use dotconnect for sqlite version 4.6.301.0

I have a table which contains a nullable datetime field named tuitiondate. The datetime is stored in the field as follows: 2013-09-16 00:00:00.000000

however, when i use a linq query to query the database, no results are returned.

e.g. code:

Code: Select all

DateTime newDate = new DateTime(2013, 9, 16);
allStudents = ctx.Students.Where(p => (DateTime)p.TuitionDate == newDate);

How do I make the linq query return my existing data?

[MariiaI]

This is a known issue and it is related to the SQLite peculiarity, because dates are stored in the database as strings and comparison of dates is performed as string comparison. Strings may be different for the same date, because the date in SQLite may be stored with different accuracy (with milliseconds, etc.) and when comparing it with the date that we pass from .NET, strings for this date can differ(e.g., date of 13/06/2012 may have representation in .NET as 13.06.2012 00:00:00.0000000, and in SQLite it can be represented as 13/06/2012 00:00:00.00). However, we are considering adding the possibility to generate code with the conversion to the SQLite datetime type. We will inform you about the results as soon as possible.

For proper date comparison you could try using the explicit datetime type conversion and run queries via the DataContext.ExecuteQuery() method, for example:

Code: Select all

YourDataContext ctx = new YourDataContext(){Log = Console.Out};
DateTime newDate = new DateTime(2013, 9, 16);
string query = "SELECT * FROM main.Students t1 WHERE date(t1.TuitionDate ) = date({0})";
IEnumerable<Student> rez = ctx.ExecuteQuery<Student>(query, newDate);

Also, a possible workaround could be find here:
http://forums.devart.com/viewtopic.php?t=24938

[MariiaI]

The bug with the date comparison is fixed.
New version of dotConnect for SQLite 5.1 is released!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=29&t=28238.