Exception on creating a query with index and predicate including DateTimeOffset using Postgresql dialect

Hi all,

The following (simplified) query using an predicate and an index

var tokens = session.Query<OpenIdToken, OpenIdTokenIndex>(
    token => token.CreationDate < threshold.UtcDateTime, collection: OpenIdToken.OpenIdCollection)
    .Take(100).ListAsync();

results in the following exception using postgresql dialect but not on sqlite:

[09:00:29 Error] OrchardCore.OpenId.Tasks.OpenIdBackgroundTask
An error occurred while pruning authorizations from the database.
System.Reflection.TargetException: Object does not match target type.
   at System.Reflection.MethodInvokerCommon.ValidateInvokeTarget(Object target, MethodBase method)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at YesSql.Services.DefaultQuery.Evaluate(Expression expression)
   at YesSql.Services.DefaultQuery.ConvertFragment(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertFragment(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertComparisonBinaryExpression(IStringBuilder builder, BinaryExpression expression, String operation)
   at YesSql.Services.DefaultQuery.ConvertFragment(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertPredicate(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertEqualityBinaryExpression(IStringBuilder builder, BinaryExpression expression, String operation)
   at YesSql.Services.DefaultQuery.ConvertFragment(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.ConvertPredicate(IStringBuilder builder, Expression expression)
   at YesSql.Services.DefaultQuery.Filter[TIndex](Expression`1 predicate)
   at YesSql.Services.DefaultQuery.Query`1.YesSql.IQuery<T>.With[TIndex](Expression`1 predicate)
   at YesSql.QueryExtensions.Query[T,TIndex](ISession session, Expression`1 predicate, Boolean filterType, String collection)
   at YesSql.QueryExtensions.Query[T,TIndex](ISession session, Expression`1 predicate, String collection)
   at OrchardCore.OpenId.YesSql.Stores.OpenIdAuthorizationStore`1.PruneAsync(DateTimeOffset threshold, CancellationToken cancellationToken)
   at OrchardCore.OpenId.Tasks.OpenIdBackgroundTask.DoWorkAsync(IServiceProvider serviceProvider, CancellationToken cancellationToken)

Full original query can be found here.

Used Version:

YesSql 3.4.0
OrchardCore 1.7.2

This only occurs when using PostgreSQL dialect. Workaround that helped but I'm not sure about the side effects:

Removing the DateTimeOffset type handler in PostgreSqlDialect

// DateTimeOffset are stored as Utc DateTimes in timesstamptz fields
// Represents a moment in time
AddTypeHandler<DateTimeOffset, DateTime>(x => x.UtcDateTime);

https://github.com/sebastienros/yessql/blob/main/src/YesSql.Provider.PostgreSql/PostgreSqlDialect.cs#L96

or changing the predicate to use a temporary date-time variable (not sure why this helped)

var thresholdUtcDateTime = threshold.UtcDateTime
var tokens = session.Query<OpenIdToken, OpenIdTokenIndex>(
    token => token.CreationDate < thresholdUtcDateTime, collection: OpenIdToken.OpenIdCollection)
    .Take(100).ListAsync();

sebastienros / yessql

Exception on creating a query with index and predicate including DateTimeOffset using Postgresql dialect #531