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.

#dotnet #entity framework core #sqlite