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: 116
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: 9029
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: 116
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: 116
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:10
Thank 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: 9029
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: 116
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)?

Post Reply