WHERE clause compares dates with milliseconds incorrectly (EF6 Code-First)
Posted: 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:
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.
DbMonitor cannot help me because it doesn't show milliseconds on Parameters tab.
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 :).
I use dotConnect for SQLite 5.14.1519 and EF 6.2.
My database has DateTime field with milliseconds like following:
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();
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 :).