So I'm running into a rather odd issue here. I've got a PostgreSQL 14 server and I'm using the Npgsql Entity Framework Core provider to access it. If I have an Entity Framework Core entity that contains a date as a NodaTime Instant type (which becomes timestamp with time zone in the database) and try to project that via .InZone(timeZone).LocalDateTime, I run into 3 situations.
Actual example query would be something like this:
await context.Entities.Select(e => e.Date.InZone(<timeZone>).LocalDateTime).ToListAsync();
(Although it seems that not including the .LocalDateTime portion has no bearing on the below situations, with the exception of when .InZone(<timeZone>) is replaced with .InUtc().)
The first situation, if in place of <timeZone> I make an explicit call to DateTimeZoneProviders.Tzdb.GetSystemDefault(), then the above query succeeds without issue. (Although oddly, when I look at EFCore's generated SQL, I do not see AT TIME ZONE like the Npgsql docs mention, but I do see it if I was to replace the .InZone(<timeZone>) call with .InUtc().)
The second situation, if in place of <timeZone> I put in a reference to a static variable containing the result of DateTimeZoneProviders.Tzdb.GetSystemDefault(), then I get an InvalidOperationException with the following message:
The client projection contains a reference to a constant expression of 'NodaTime.TimeZones.CachedDateTimeZone' which is being passed as an argument to the method 'InZone'. This could potentially cause a memory leak; consider assigning this constant to a local variable and using the variable in the query instead. See https://go.microsoft.com/fwlink/?linkid=2103067 for more information.
Given the above, I run into the third situation, if in place of <timeZone> I use a local variable that contains either the result of DateTimeZoneProviders.Tzdb.GetSystemDefault() or the static variable I mentioned above, then I get an InvalidOperationException with the following message:
Expression '@__timeZone_0' in the SQL tree does not have a type mapping assigned.
Am I doing something wrong here? My expected goal is to be able to take the Instant that I'd get from the database's timestamp with time zone and have it be either a ZonedDateTime or LocalDateTime on the client end, without needing to go through extra steps on the client end after obtaining the data from the database. Right now I would have to get the date from the database using .InUtc().LocalDateTime and then after the list has been materialized, I'd be able to do conversions on the returned values.
The NodaTime plugin doesn't support passing parameterized time zones from .NET; you can only use a constant expression such as DateTimeZoneProviders.Tzdb["Europe/Berlin"] (see these docs for supported translations). You can use this to convert a PG timestamp with time zone into a timestamp without time zone in PG, and then retrieve the result as a timestamp without time zone, which can be read as a NodaTime LocalDateTime.
However, if what you're looking for is to get a ZonedDateTime/LocalDateTime with the time zone of the client machine (where .NET is executing), then doing .InUtc().LocalDateTime is the way to do that - any reason you're trying to avoid it?
A bit more context (as well as a full code sample) could shed some more light on what you're trying to achieve.