WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by bairog » Wed 04 Dec 2019 16:44

Hello.
I use dotConnect for SQLite 5.14.1519 and EF 6.2.
My database has DateTime field with milliseconds like following:
Image

And I'm selecting items that are for example < DateTime.Parse("06.11.2019 1:00:00.020") and >= DateTime.MinDate.
When I select them using WHERE clause in Linq to Entities it works incorrectly and return me 1 item,
when cast ToList() first and then use WHERE clause in Linq to Objects - it works correctly and return me 2 items.

Code: Select all

//values is IQueryable<>
//maxDate is for example DateTime.Parse("06.11.2019 1:00:00.020")
//minDate is for example DateTime.MinDate

//WHERE INSIDE LINQ TO ENTITES IS NOT WORKING CORRECTLY (1 VALUE IS RETURNED INSTEAD OF 2)
var summableFieldsList1 = values.Where(v => (v.HistoricalMomentDate.CompareTo(maxDate) < 0) && (v.HistoricalMomentDate.CompareTo(minDate) >= 0)).ToList();

//CAST TO LIST FIRST AND THEN WHERE INSIDE LINQ TO OBJECTS IS WORKING CORRECTLY (2 VALUES ARE RETURNED)
var summableFieldsList = values.ToList().Where(v => (v.HistoricalMomentDate.CompareTo(maxDate) < 0) && (v.HistoricalMomentDate.CompareTo(minDate) >= 0)).ToList();
DbMonitor cannot help me because it doesn't show milliseconds on Parameters tab.
Image

I've uploaded my code to run as-is here - line 120 ans 123 in SysTypesDB.SQLITE.EF\LogBookStorage.cs. Sorry for too much code - just copy-pasted a part from my project :).
Last edited by bairog on Thu 09 Jan 2020 07:36, edited 1 time in total.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by Shalex » Fri 06 Dec 2019 19:10

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by bairog » Sat 07 Dec 2019 05:29

Shalex wrote: Fri 06 Dec 2019 19:10 Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.
Thank you, waiting for a solution ASAP.

P.S. If it's possbile - update DbMonitor as well to show milliseconds for a DateTime on Parameters tab. It will be very helpfull for debugging in future. Many thanks.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by bairog » Thu 09 Jan 2020 07:32

Shalex wrote: Fri 06 Dec 2019 19:10Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.
Happy new year and merry christmas.
Any progress for now? More than a month has passed..

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by Shalex » Thu 16 Jan 2020 15:12

Merry Christmas and Happy New Year!
The investigation is in progress. As soon as we have any results, we will contact you.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by bairog » Fri 21 Feb 2020 04:55

Hello again.
Any progress for now - maybe in latest build (v.5.15.1583)?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by Shalex » Tue 25 Feb 2020 16:24

The issue has been fixed.
maybe in latest build (v.5.15.1583)?
No, the fix was implemented after releasing the public build. Contact us and specify your email used when registering the product for getting the internal build.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by bairog » Thu 27 Feb 2020 06:06

Shalex wrote: Tue 25 Feb 2020 16:24No, the fix was implemented after releasing the public build. Contact us and specify your email used when registering the product for getting the internal build.
I've obtained and tested internal build (5.15.1587) - everything is working as expected. Looking forward for a public build.

P. S. If it's possbile - please update DbMonitor as well to show milliseconds for a DateTime on Parameters tab. It will be very helpfull for debugging in future. Many thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by Shalex » Thu 27 Feb 2020 12:13

please update DbMonitor as well to show milliseconds for a DateTime on Parameters tab. It will be very helpfull for debugging in future.
We will notify you when the issue is fixed.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by Shalex » Sat 04 Apr 2020 12:50

The bug with comparing milliseconds of DateTime properties is fixed in v5.15.1612: viewtopic.php?f=29&t=40463.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)

Post by Shalex » Mon 21 Sep 2020 16:26

The Devart.Common.DbMonitor class is improved: now values of the DateTime parameters sent to the dbMonitor tool are supplemented with milliseconds: viewtopic.php?f=29&t=42250.

Post Reply