.Net Core 7 with Postgres Can't cast database type timestamp with time zone to LocalDateTime

Question

I've read some very similar posts to this one but I have yet to find an answer.

What I'm trying to do is get a timestamp back from the database, convert it into the users timezone, then convert that to a string - preferably in an EF Linq statement.

Here's a sample of a smaller Linq statement that fails for me.

model.PreviousClasses = await _context.ClassOfferingEnrollments.AsNoTracking().Where(x =>
                    x.WorkerId == workerId
                    && x.Offering != null
                    && !x.ArchivedDate.HasValue
                    && (x.Offering.ElectionId == null || x.Offering.Election.ElectionDate < currentDate))
                .Select(x => new PreviousClassesViewModel()
                {
                    ElectionName = x.Offering.Election.Name,
                    LocationName = x.Offering.Location.Name,
                    ClassOfferingType = x.Offering.Type,
                    StartTime = x.Offering.ClassStartTime.Value.InZone(_currentCountyTimeZone).LocalDateTime,
                    EndTime = x.Offering.ClassEndTime.Value.InZone(_currentCountyTimeZone).LocalDateTime,
                    InstructorName = x.Offering.Instructor != null
                        ? x.Offering.Instructor.FirstName + "_" + x.Offering.Instructor.LastName
                        : null
                }).ToListAsync();

The part failing is the InZone(_currentCountyTimeZone) line. I've tried this as well:

x.Offering.ClassStartTime.Value.InZone(DateTimeZoneProviders.Tzdb["America/New_York"]).LocalDateTime

I also read that InZone has some issues so I attempted this from another post as well:

x.Offering.ClassStartTime.Value.InUtc().LocalDateTime

All of these queries give the same exception:

{"Can't cast database type timestamp with time zone to LocalDateTime"}

I've read several other posts so I've set a few other things as well. My startup has these in it:

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

services.AddEntityFrameworkNpgsql()
                .AddDbContext<ElectionWorkerPortalContext>(options => options.UseNpgsql(defaultConnectionString, o => o.UseNodaTime()))

I also tried setting this on my builder via:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.UseNpgsql(o => o.UseNodaTime());

But UseNodaTime() doesn't exist as a method on that object.

I've got the following installed via Nuget:

  <ItemGroup>
    <PackageReference Include="AWSSDK.S3" Version="3.7.103.1" />
    <PackageReference Include="AWSSDK.SecretsManager" Version="3.7.101.21" />
    <PackageReference Include="CsvHelper" Version="12.1.2" />
    <PackageReference Include="DataTables.AspNet.AspNetCore" Version="2.0.2" />
    <PackageReference Include="Microsoft.AspNetCore.Authentication.JwtBearer" Version="7.0.2" />
    <PackageReference Include="Microsoft.AspNetCore.Authentication.OpenIdConnect" Version="7.0.2" />
    <PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="7.0.2" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc.NewtonsoftJson" Version="7.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.2">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.Extensions.Options.ConfigurationExtensions" Version="7.0.0" />
    <PackageReference Include="Microsoft.IdentityModel.Clients.ActiveDirectory" Version="5.3.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="7.0.0" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="7.0.3" />
    <PackageReference Include="Mindscape.Raygun4Net.AspNetCore" Version="6.6.6" />
    <PackageReference Include="Newtonsoft.Json" Version="13.0.2" />
    <PackageReference Include="NodaTime.Serialization.JsonNet" Version="3.0.1" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="7.0.3" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime" Version="7.0.3" />
    <PackageReference Include="Rotativa.AspNetCore" Version="1.2.0" />
    <PackageReference Include="Serilog.Enrichers.Environment" Version="2.2.0" />
    <PackageReference Include="Serilog.Enrichers.Process" Version="2.0.2" />
    <PackageReference Include="Serilog.Enrichers.Thread" Version="3.1.0" />
    <PackageReference Include="Serilog.Extensions.Logging" Version="3.1.0" />
    <PackageReference Include="Serilog.Formatting.Compact" Version="1.1.0" />
    <PackageReference Include="Serilog.Settings.Configuration" Version="3.4.0" />
    <PackageReference Include="Serilog.Sinks.File" Version="5.0.0" />
    <PackageReference Include="System.Drawing.Common" Version="7.0.0" />
  </ItemGroup>

I think those are all the details. I don't mind installing another package if necessary, the only thing I'm unwilling to do is downgrade .Net. I should note, I was using core 3.1 prior to this upgrade and everything worked. I'm an experienced dev, but far from an expert on timezones (its complex), so any assistance is welcome! I believe I have the latest of everything currently and I'm just trying to get it stable once again. Thanks!

Answer 1

I ended up creating an extension method which seems to do the job. Instead of calling:

.InZone(_currentTimeZone)

where _currentTimeZone was a DateTimeZone. I now call my extension like this:

.InZoneExtension(_currentTimeZone)

only now _currentTimeZone is a string and I use it like this:

public static ZonedDateTime InZoneExtension(this Instant instant, string timezone)
        {
            DateTimeZone zone = DateTimeZoneProviders.Tzdb[timezone];

            return instant.InZone(zone);
        }

Hopefully this helps someone. If anyone knows of a better solution I'd still love to know.