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!