Querying a DateTimeOffset in a jsonb fails with InvalidCastException

[Kronos11]

Problem When attempting a LINQ query using a nested POCO that has a DateTimeOffset? property, the linq query will fail to execute with an exception. It appears that the actual SQL generation succeeds (it's visible in the debugger output), but the actual generation of the query parameters fails. The exception is as follows:

System.InvalidCastException: Can't write CLR type System.DateTimeOffset with handler type TextHandler
         at lambda_method44(Closure , NpgsqlTypeHandler , Object , NpgsqlLengthCache& , NpgsqlParameter )
         at Npgsql.TypeHandling.NpgsqlTypeHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
         at Npgsql.NpgsqlParameter.ValidateAndGetLength()
         at Npgsql.NpgsqlCommand.ValidateParameters(ConnectorTypeMapper typeMapper)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
         at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
         at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()

Example:

var sixHoursBefore = DateTimeOffset.UtcNow.AddHours(-6);
var workItem = DatabaseFixture.GetContext(true).WorkItems
    .FirstOrDefault(wi => wi.UserDetails.AssignedDate < sixHoursBefore );

where UserDetails is a poco that has AssignedDate as a DateTimeOffset

The strange thing is it generates SQL that is correct, but it blows up when validating the parameter

SELECT w."Id", w."UserDetails" FROM "WorkItems" AS w WHERE (w."Status" = 1) AND (w."UserDetails"->>'AssignedDate' > '2021-06-11T16:08:55.9793830+00:00' ) LIMIT 1

[Kronos11]

I believe adding DateTimeOffset here would potentially fix the issue: https://github.com/npgsql/efcore.pg/blob/099131b28bc2cf54951a2ee037f38a7afb6092a7/src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlJsonPocoTranslator.cs#L117

[roji]

@Kronos11 thanks, makes sense. I've submitted #1884 to fix this (and improve the tests).

[Kronos11]

Thanks for the quick fix!