Page 1 of 1
Linq - Filtering by date doesn't work
Posted: Wed 02 Oct 2013 05:46
by 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?
Re: Linq - Filtering by date doesn't work
Posted: Wed 02 Oct 2013 10:52
by 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
Re: Linq - Filtering by date doesn't work
Posted: Fri 01 Nov 2013 06:11
by 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.