Lessons Learnt: Migrating From Net Framework to Net Core - Changing SQLite Providers

I mentioned back in the first post how we swapped SQLite providers during our port from .Net Framework to .Net Standard and .Net Core. Previously we used System.Data.SQLite and we now use Microsoft.Data.SQLite.  In order to support some older .Net 4.5 projects we use multi targetting and some #ifdefs to swap the providers without any real knowledge to the calling applications.

For the most part this worked without a hitch however today I want to highlight a difference between the providers which doesn't cause a build error and ended up being caught very late on in testing.

Rightly or wrongly a decision was made a long time ago to store a date time value in a sqlite db not as ticks but as a string, 2019-05-08T17:14:26.3441705+01:00 for example. It wrongly wasn't being stored as UTC but there was no visible side effect of this in its use case and this has been in the codebase for several years.

During recent final testing it was noted that it appeared just one table of data in our system was always out by an hour. My years of experience immediately made me think this is a DST isssue and we aren't storing UTC and then converting or something to that effect. Previous versions of the product all checked out fine and the bug only happens on our newly ported version. Quick check of the commit history highlighted nothing had changed in this area of code between releases, only the SQLite provider.

I'd like to say we had ample Test coverage in this area however unfortunately we don't. So I took to writing some new tests to prove that the DateTime parsing was working correctly, turns out it was fine but then to port these back to the previous version to see the difference. Turns out.... there were two differences one of them significant...



Did you spot the differences.... So the old implementation System.Data.SQLite didn't parse the Millisecond's AND it's Kind is set to Unspecified, where as the new implementation Microsoft.Data.SQLite correctly parses the Milliseconds and sets the Kind to Local. This then meant the DateTime was be interepted differently at the server. Annoyingly the old implementation has the real bug in it but as the services expect the incorrect implementation we are having to "manipulate" the DateTime before sending it up to the server so that the Kind is still Unspecified by using the DateTime.SpecifyKind method.

So the real lesson learnt here.... write more tests!  Even if it's just to prove things you would expect to take for granted.


Popular posts from this blog

WebUSB - An unexpected update...

Can you use BuildRoot with Windows Subsystem for Linux......

DotNet CLI , private NuGet feeds and Linux...