Devart Forums

Posted: **Wed 19 Sep 2012 21:05**

I've been in the process of moving some code over from MS SQL to use Sqlite and I've been having some issues with date comparisons. Given the following LINQ query works just fine in MS SQL

var q1 = (from rd1 in rollingDB.storageRolls
where rd1.nowDateTime.Date == DateTime.Now.Date
orderby rd1.nowDateTime.Date
select rd1);

I understand that Sqlite stores it's dates in a string format and does not have column formats. However, I was under the impression that LINQconnect was able to handle the simple conversion/comparsions of this nature?

Any suggestions as to how to do simple LINQ date comparsions with sqlite?
Thanks in advance!
Dr. Leif

Posted: **Thu 20 Sep 2012 11:30**

This is a known issue. We are working on it and will inform you when it is fixed.

It is related to SQLite peculiarity, because dates are stored in the database as strings and comparison of dates is performed as string comparison. The problem is that in the query the "DateTime.Now.Date" is a constant of the DateTime type, hence it is passed to the server with the time, while the function Date in "rd1.nowDateTime.Date" cuts the time in the date. For this reason, a comparison of the same dates gives wrong results.

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

Code: Select all

YourDataContext rollingDB = new YourDataContext(){Log = Console.Out};
DateTime data = DateTime.Now.Date;
string query = "SELECT * FROM main.storageRolls t1 WHERE date(t1.nowDateTime) = date({0})";
IEnumerable<storageRoll> rez = rollingDB.ExecuteQuery<storageRoll>(query, data);

Please notify us if this helps.

Posted: **Tue 25 Sep 2012 14:16**

Thanks for the reply!

I've found a work around that allows me to continue to use LINQ and seems to produce the results that I'm looking for, however; I'm still doing some testing.
INSTEAD OF THIS:
var q1 = (from rd1 in rollingDB.storageRolls
where rd1.nowDateTime.Date == DateTime.Now.Date
orderby rd1.nowDateTime.Date
select rd1);

I'm using this:
var qR = (from rd1 in rollingDB.storageRolls
where rd1.nowDateTime.Month == DateTime.Now.Month
&& rd1.nowDateTime.Day == DateTime.Now.Day
&& rd1.nowDateTime.Year == DateTime.Now.Year
select rd1);

As I stated above so far I'm getting the results that I'm looking for. Hope this helps someone else out there.
Dr. Leif

Posted: **Wed 26 Sep 2012 08:36**

Thank you for sharing the workaround.

Posted: **Fri 01 Nov 2013 06:10**

The bug with the date comparison is fixed.
New build of LinqConnect 4.4.364 is available for download now!
It can be downloaded from http://www.devart.com/linqconnect/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=31&t=28242.

Posted: **Thu 07 Nov 2013 10:17**

This still does not work if the DateTime column is nullable.

e.g.

var today = DateTime.Today;
var orders =
(from o in dbContext.OrderDetails
where o.OrderDate.HasValue
&& o.OrderDate.Value.Date == today.Date
select o).ToList();

fails with exception

System.NotSupportedException occurred
HResult=-2146233067
Message=Cannot use "DATETIME" type value in "Boolean" type argument
Source=Devart.Data.Linq
StackTrace:
at Devart.Data.Linq.Engine.DbMethodCallConverter.CheckConversionToParameterType(SqlExpression , Type )
at Devart.Data.Linq.Engine.DbMethodCallConverter.VisitBinaryOperator(SqlBinary )
at Devart.Data.Linq.Engine.SqlVisitor.Visit(SqlNode )
at Devart.Data.Linq.Engine.SqlVisitor.VisitExpression(SqlExpression )
at Devart.Data.Linq.Engine.SqlVisitor.VisitSelectCore(SqlSelect )
at Devart.Data.Linq.Engine.DbMethodCallConverter.VisitSelect(SqlSelect )
at Devart.Data.Linq.Engine.SqlVisitor.VisitAlias(SqlAlias )
at Devart.Data.Linq.Engine.DbMethodCallConverter.VisitAlias(SqlAlias )
at Devart.Data.Linq.Engine.SqlVisitor.Visit(SqlNode )
at Devart.Data.Linq.Engine.QueryCompiler. (SqlNode , ResultShape& )
at Devart.Data.Linq.Engine.QueryCompiler. (Expression )
at Devart.Data.Linq.Engine.QueryCompiler.Compile(Expression )
at Devart.Data.Linq.DataProvider.7axdkwt3gfx52xqzykge2d4jpayt4hd5 (Expression )
at Devart.Data.Linq.Engine.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Posted: **Thu 07 Nov 2013 13:09**

Thank you for the report on this. We will inform you when this issue is fixed.
As a workaround, you could re-write your query in the following ways:

Code: Select all

 var today = DateTime.Today;
 var orders =
           (from o in dbContext.OrderDetails
            where o.OrderDate.HasValue
            select o);
 var results = orders.Where(o => o.OrderDate.Value.Date == today.Date).ToList();

or

Code: Select all

var results2 = dbContext.OrderDetails.Where(o => o.OrderDate.HasValue).Where(o => o.OrderDate.Value.Date == today.Date).ToList();

Posted: **Thu 14 Nov 2013 12:42**

The bug with comparison (AND/OR) of dates with another comparison results or boolean expressions is fixed.
New build of LinqConnect 4.4.374 is available for download now!
It can be downloaded from http://www.devart.com/linqconnect/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=31&t=28335.

Devart Forums

LINQconnect sqlite date comparisons

LINQconnect sqlite date comparisons

Re: LINQconnect sqlite date comparisons

Re: LINQconnect sqlite date comparisons

Re: LINQconnect sqlite date comparisons

Re: LINQconnect sqlite date comparisons

Re: LINQconnect sqlite date comparisons

Re: LINQconnect sqlite date comparisons

Re: LINQconnect sqlite date comparisons