EntityFramework Core 6 & PostgreSQL Date Handling
So I was purring along writing my application using blazor and testing persistence using sqlite. I decided to start my application locally as it would be in production using a postgres database. Ran migrations and everything worked great until it came time to run the application and I got a big fat exception.
Cannot write DateTime with Kind=UTC to PostgreSQL type ‘timestamp without time zone’
I was stumped and not sure what to make of it. I headed over to google and realized its become a common error being encountered. My search led me back to the Npgsql docs and I realized that there was a section in the 6.0 release notes that indicated there are breaking changes relating to how the driver translates the DateTime type. Here is the page for more details.
In order to solve my problem, I chose to use the DateOnly type which is new in .Net 6 and I found this type to be just what I wanted. The Npgsql documentation recommends using NodaTime but I didn’t want to deal with yet another library when the built-in .NET types work just fine.
So I changed all my entity and viewmodel properties to DateOnly type, re-ran my migrations and the type generated, as translated by Npgsql, is date. So in the postgresql database, all my dates are now of the date type and I don’t have to deal with bogus time appendage to my dates as a bonus!
Hope this saves someone the 30 mins I lost googling, reading and reimplementing my code.
God bless.