Recently, ASP.NET Core 3.0 was released, along with all it's supporting libraries like Entity Framework Core. In the process of migrating Dangl.Identity over to the new version, I discovered that some integration tests failed with this message:

System.NotSupportedException : SQLite cannot order by expressions of type 'DateTimeOffset'. Convert the values to a supported type or use LINQ to Objects to order the results.

The error message is pretty clear - SQLite with Entity Framework Core 3.0 does no longer support some operations when using DateTimeOffset properties in database models, as specified in the official Microsoft Guidelines on limitations with SQLite.

The recommendation to switch to a supported type is great, but what to use? Falling back to regular DateTime, you'll lose the time zone information. Even if you're storing only UTC dates, while ensuring you're never making an error anywhere you touch dates, Entity Framework will always return a DateTimeKind.Unspecified when retrieving values from the database. While you can work around that with some conversion via an EntityMaterializerSource, this feels awkward and error prone.

Luckily, aptly named user bugproof on GitHub posted a great snippet that attaches a built-in converter for all properties in your database model. Here's how I've implemented it in my database context class:

The only drawback is that the conversion only supports up to millisecond precision, but for most uses cases this is likely not a problem. In case you're comparing values, simply trim the last three digits from your values in your test code and you're good to go:

Happy modelling!