[C#/LINQ/EF] EF benadert DateTime als DateTime2

[Contagion]

In navolging van mijn vorige droevige verhaal aangaande het Entity Framewok -> LINQ -> SQL welke door bugs in SQL Server verschillende resultaten oplevert, nu een volgende:

Een tabel in SQL Server 2008 met DateTime velden wordt door dat idiote Entity Framework gevuld als zijnde DateTime2 velden. Dat hoeft heen probleem te zijn maar wat blijkt. De 'loss of precision' geeft bij een 'WHERE' clause door de afrondingsfouten geen of verkeerde waarden terug.

Wat gebeurt er:

1. Maak een SQL 2008 DB met tabel met daarin 1 DateTime field. Maak een C# DateTime object met de waarde '2011-08-03 21:13:00.8258922' (== new DateTime(634480027808258922), de waarde in ticks)
2. Gebruik het Entity Framework om een modelletje te maken van de DB en maak een programmaatje die de DateTime insert in de tabel.
3. De SQL Query die het Entity Framework genereert zal hierop lijken:
   
   code:
   

   exec sp_executesql N'
   INSERT [testtab]
   values (@0)',
   N'@0 datetime2(7)',
   @0='2011-08-03 21:13:00.8258922'

4. In de database zie je nu de volgende DateTime verschijnen: 2011-08-03 21:13:00.827. Interessant: Een 7 en niet een 6 aan het eind?! Zal ook wel met de precision te maken hebben... En je ziet dus dat hij doodleuk een DateTime2 gaat inserten ook al in het veld van type DateTime.
5. Maak een vergelijking in C# als: db.TestTable.Where(x=>x.mijnDate == new DateTime(634480027808258922). De SQL Query die hieruit rolt zal (sterk vereenvoudigd!) iets zijn als:
   
   code:
   

   exec sp_executesql N'
   SELECT 
   COUNT(*)
   FROM testtab
   WHERE testDateTime = @0',
   N'@0 datetime2(7)',
   @0='2011-08-03 21:13:00.8258922'

6. Je verwacht ten minste 1 resultaat van je zojuist geinserte DateTime maar het resultaat van de count is altijd 0 omdat de meegegeven DateTime2(7) = 2011-08-03 21:13:00.8258922 en dat komt niet overeen met de in de tabel aanwezig zijnde 2011-08-03 21:13:00.827! En ook al doet het Entity Framework het fout en zou hij voor DateTime2(3) kiezen, dan nog werkt het niet want dan komt de afronding op .826 en in de DB staat inmiddels .827.

Bedenk ik nou altijd zulke ingewikkelde dingen en is er echt (bijna) niemand anders op de wereld die vrijwel direct tegen dit soort idiote problemen oploopt?!? En kan MS dat zelf niet met een unity test o.i.d. zo iets direct doorzien en oplossen?! Wie bedenkt trouwens dat het EF van een DateTime veld een DateTime2 veld maakt!? Er is wel een rapportje: MSDN: Entity Framework uses datetime2 format with a field of type datetime



Iemand die een mooie oplossing heeft of wordt het stomweg omzetten van DateTime velden naar DateTime2 in de database? If so, misschien dat bovenstaande een les/hulp kan zijn voor anderen...

[xardy]

probleem lijkt mij dat de datetime niet accuraat genoeg is.

Als ik op msdn bij datetime kijk zie ik het volgende staan:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Dus datetime2 heeft een grotere precisie en lost waarschijnlijk jouw probleem op (niet getest).

[Contagion]

Ik geloof ook dat DateTime2 het probleem kan oplossen. Ik vind het alleen heel raar dat een framework wat zou moeten zorgen voor automatisering van allerlei taken (het op een object-georienteerde manier benaderbaar maken van een database, automatische en op een juiste manier converteren van datatypen, etc.) dit soort elementaire problemen oplevert. Zonder blikken of blozen wordt er een INSERT gedaan naar een verkeerd datatype met daarbij dus verlies van data. Eerder draaide dit onder SQL 2005 en dat leverde totaal geen problemen op (daar bestaat DateTime2 niet en dit wordt een INSERT met type DateTime2 ook niet geprobeerd door LINQ->SQL).

Maar dan nog, loss of precision: prima, maar vervolgens bij een SELECT ... WHERE niet dezelfde type conversie aanhouden waardoor je een zojuist geinsert record niet meer terug krijgt, dat vind ik echt niet kunnen! En ik zal niet de eerste of laatste zijn die iets ontwerpt met het Entity Framework die niet verwacht dat een zo eenvoudige operatie al niet het gewenste resultaat oplevert. Misschien zijn er dus wel honderden programma's die niet altijd doen wat je verwacht om zo'n soort 'bug', terwijl de developer er geen idee van heeft/heeft gehad dat een dergelijk gedrag zich voordoet. Ik kan in c# namelijk niet kiezen voor iets anders dan het objecttype DateTime, dus dat is wat het is. Het Entity Framework zorgt voor de rest en maakt de conversies. Ik kan ook een 'null' in mijn object stoppen die dan automatisch naar sqltype DBNull wordt vertaald. Maar hier gebeurt dat dus niet op een juiste manier, zonder ook maar een waarschuwing te geven of wel simpelweg het type te gebruiken dat ook in de database gespecificeerd is (datetime dus en geen datetime2).

Je snapt m'n frustratie waarschijnlijk wel en een paar dagen geleden had ik ook al een dergelijk akkefietje waarbij EF -> LINQ -> SQL een query zo ingewikkeld had gemaakt dat de resultset op een multicore omgeving niet consistent is...

[Niemand_Anders]

Dus omdat jij de documentatie van SQL2008 niet leest en daardoor niet het verschil (en oplossingen) kent tussen een CLR DateTime en een SQL DateTime waarde, doet Microsoft het fout. Als *JIJ* in je database datetime2 gebruikt (is een CLR versie en geen native) dan hebben beide dezelfde implementatie. Als je namelijk wel de documentatie had gelezen dan had je geweten dat datetime in SQL server de tijd wordt afgerond op stappen van of .000, .003, or .007 seconds.

Omdat DateTime2 een CLR implementatie is is de performance wel iets lager. Dat is een tradeoff welke alleen jij kunt maken. Wil je full speed? Vergeet datums en gebruikt een bigint veld om het aantal ticks op te slaan..

[Contagion]

Dat heeft natuurlijk niks te maken met het wel of niet lezen van documentatie. Als ik kies in SQL 2008 voor een datetime veld met de bijbehorende precisie, dan kies is daarvoor. Als ik vervolgens kies om gebruik te maken van het Entity Framework, dan is dat ook een keuze. Dat het Entity Framework er vervolgens voor kiest om een C# DateTime in een SQL Server database te benaderen als datetime2 ook al is het veld in de SQL 2008 databasetabel een datetime, dan heb ik daar geen zeggenschap in en bovendien is het onlogisch, onverwacht en in mijn ogen ook onjuist.

Maar ook hier, met het beschreven gedrag zou ik kunnen leven als de LINQ query: db.table.where(x=>x.sqldatetimefield == CSharpDateTimeObject) resultaat zou opleveren nadat ik een db.AddTotable(ObjectMetZelfdeCSharpDateTime) heb gedaan, alleen dat doet het niet.