WaitForEvent Timezone issue with PostgreSQL and .NET 6

[pbros]

There seems to be an issue with timezone processing in relation with the breaking changes in the 6.0 release of Npgsql

https://www.npgsql.org/efcore/release-notes/6.0.html#major-changes-to-timestamp-mapping

To reproduce the issue, you need a WaitFor() workflow step with an effectiveDate = DateTime.UtcNow

Then, publish the event that this step is waiting for. In .NET 5, this worked without issues, but in .NET 6 with Npsql 6.0+, the published event does not meet the requirements of the WaitFor() event and the workflow does not continue.

From our investigation, if we set the effectiveDate of the WaitFor() step to DateTime.UtcNow.AddHours(-4); (our current timezome offset to UTC), it works without issue.

According to the Npsql documentation, the fix would be to run a new migration on the PostgresContext, which will update the DateTime columns from timestamp to timestamptz

Finally, to validate that this was indeed the issue, we applied the workaround here https://www.npgsql.org/efcore/release-notes/6.0.html#opting-out-of-the-new-timestamp-mapping-logic by adding AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true); at the beginning of our Main() and we had the correct behavior (e.g. waiting for an event with an effective date and publishing an event right away made our workflow continue)

[pbros]

@danielgerlag I wanted to create a PR with a new migration for PostgreSQL using .NET6. However, I experienced some difficulties creating it since the project targets both .NET Standard 2.1 and .NET 6, and the CreateDatabase method hard-codes a connection string of 127.0.0.1 with username and password.

Do you have a standard and/or documented way of generating new Migrations for the providers?

Also, are there any plans of dropping support .NET Standard 2.1 as it it preventing from using EF 6 across the board

See https://learn.microsoft.com/en-us/ef/core/miscellaneous/platforms

I'm also unsure how the migrations could support 2 different version of NPSQL...