We have an existing Database First (e.g. Scaffolded) .Net 6 project attached to PostgreSQL 13 DB.

The DB has a lot of timestamptz aka TIMESTAMP WITH TIMEZONE fields. So we use NodaTime.

Time(!) came to upgrade from .Net 6 > 8, and with it, EF Core from 6 > 8. NPGSQL (inc NodaTime) from 6 > 8.

Relevant packages (I think) include:

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.11">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.11">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.11" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime" Version="8.0.11" />

New EF Core came with new T4 Templates allowing us to do our custom Scaffolding. The existing project needed some "finessing" to account for various .Net 8 changes. But finally... all good... no errors... starts up fine.

Until a REST controller reads a table that contains a timestamptz field (in fact ALL tables contain these fields, this is the first table read in the pipeline)

myEntity has several Instant fields / properties correctly scaffolded...

// Find the exact record or an "unlinked" record
myEntity? existingEntity = await _db.myEntity
  .Where(u => u.someStringField == _ctx.someString)
  .OrderByDescending(u => u.someString == whatever)
      .ThenByDescending(u => u.someOtherString == alsoWhatever)
  .FirstOrDefaultAsync();

Throws...

An exception occurred while iterating over the results of a query for context type 'namespace.to.Scaffold.OurDBContext'. System.InvalidCastException: Reading as 'NodaTime.Instant' is not supported for fields having DataTypeName 'timestamp with time zone'

at Npgsql.Internal.AdoSerializerHelpers.<GetTypeInfoForReading>g__ThrowReadingNotSupported|0_0(Type type, PgSerializerOptions options, PgTypeId pgTypeId, Exception inner)
at Npgsql.Internal.AdoSerializerHelpers.GetTypeInfoForReading(Type type, PgTypeId pgTypeId, PgSerializerOptions options)
at Npgsql.BackendMessages.FieldDescription.<GetInfo>g__GetInfoSlow|50_0(Type type, ColumnInfo& lastColumnInfo)
at Npgsql.BackendMessages.FieldDescription.GetInfo(Type type, ColumnInfo& lastColumnInfo)
at Npgsql.NpgsqlDataReader.<GetInfo>g__Slow|133_0(ColumnInfo& info, PgConverter& converter, Size& bufferRequirement, Boolean& asObject, <>c__DisplayClass133_0&)
at Npgsql.NpgsqlDataReader.GetFieldValueCore[T](Int32 ordinal)
at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
at lambda_method310(Closure, DbDataReader, Int32[])
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.ReadObject(DbDataReader reader, Int32 ordinal, ReaderColumn column)
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.ReadRow()
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.InitializeAsync(DbDataReader reader, IReadOnlyList`1 columns, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.InitializeAsync(IReadOnlyList`1 columns, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.InitializeAsync(IReadOnlyList`1 columns, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

My problem is System.InvalidCastException: Reading as 'NodaTime.Instant' is not supported for fields having DataTypeName 'timestamp with time zone' is exactly the right conversion. The tooling / scaffolding worked... Its almost as if NodaTime wasn't being configured correctly in the app start up...

// Program.cs
builder.Services.AddPooledDbContextFactory<OurDBContext>((sp, options) =>
        options
          .UseNpgsql(builder.Configuration.GetConnectionString("connstring"),
          o => o
            .UseNodaTime()
            .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
        )
        .AddInterceptors(sp.GetRequiredService<UserConnectionInterceptor>())
#if DEBUG

      .EnableSensitiveDataLogging(true)
#endif
);

Its a factory because I am injecting Request Context (i.e. like a Tenant ID) into the DBContext. This hasn't changed since before the upgrade.

The kicker is... this project has a sister .Net 8 project pointing at a different database... started from scratch, but with a lot of "shared" code...e.g. most of it. And it works fine. I have tried to rule out ALL of the differences.. but still can't seem to get the upgraded project to work.

I've tried purging the bin and obj folders, thinking it might be cobwebs. No luck.

I've tried creating a new project to repro and share the problem... but no.. that works too.

I saw that NPGSQL 8 had a massive PR / Commit introducing Native AOT - and that messed around with type handlers / converters.

I should point out that we also upgraded Hot Chocolate 12 > 14 (graphql) which includes NodaTime packages and handling... but the Exception was not invoking GQL.

So unfortunately, I have to ask without sharing my exact project... where do I look next? What is not configured correctly? What does the stack trace suggest?