Exception when filter with Contains by DateTime? column

[Trimetral]

I'm not sure if it's a bug or I'm doing somehing wrong and I couldn't find anything related

So I have a filter that worked with MSSQL. EF version 3, 2008 sql server
After migration to pgsql 18 and ef 9.0.4 it starts throwing exeptions

data = data.Where(x => dates.Contains(x.Date));

dates is a List<DateTime?>
x.Date is DateTime?, in database is timestamptz nullable

Exception is

System.MissingMethodException: Constructor on type 'Microsoft.EntityFrameworkCore.Storage.Json.JsonCollectionOfNullableStructsReaderWriter`2[[System.Nullable`1[[System.DateTime, System.Private.CoreLib, Version=9.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]][], System.Private.CoreLib, Version=9.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.DateTime, System.Private.CoreLib, Version=9.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]' not found.
         at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)
         at System.Activator.CreateInstance(Type type, Object[] args)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlArrayTypeMapping`3.CreateParameters(String storeType, RelationalTypeMapping elementMapping)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlArrayTypeMapping`3..ctor(String storeType, RelationalTypeMapping elementTypeMapping)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlArrayTypeMapping`3..ctor(RelationalTypeMapping elementTypeMapping)
         at InvokeStub_NpgsqlArrayTypeMapping`3..ctor(Object, Span`1)
         at System.Reflection.MethodBaseInvoker.InvokeWithOneArg(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         --- End of inner exception stack trace ---
         at System.Reflection.MethodBaseInvoker.InvokeWithOneArg(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)
         at System.Activator.CreateInstance(Type type, Object[] args)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlTypeMappingSource.FindCollectionMapping(RelationalTypeMappingInfo info, Type modelType, Type providerType, CoreTypeMapping elementMapping)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.<>c.<FindMappingWithConversion>b__8_0(ValueTuple`4 k, RelationalTypeMappingSource self)
         at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd[TArg](TKey key, Func`3 valueFactory, TArg factoryArgument)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.FindMappingWithConversion(RelationalTypeMappingInfo mappingInfo, Type providerClrType, ValueConverter customConverter)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.FindMapping(Type type, IModel model, CoreTypeMapping elementMapping)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingsOnItemAndArray(SqlExpression itemExpression, SqlExpression arrayExpression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryableMethodTranslatingExpressionVisitor.TranslateContains(ShapedQueryExpression source, Expression item)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.<VisitMethodCall>g__TranslateAsSubquery|33_0(Expression expression, <>c__DisplayClass33_0&)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression, Boolean applyDefaultTypeMapping)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
         at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)

So I tried to use not nullable DateTime to filter to split felters, but couldn't do it either with exception

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
       ---> System.ArgumentException: Expression of type 'System.DateTime' cannot be used for parameter of type 'System.Nullable`1[System.DateTime]' (Parameter 'arg0')
         at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
         at System.Linq.Expressions.Expression.Invoke(Expression expression, Expression arg0)
         at System.Linq.Expressions.Expression.Invoke(Expression expression, IEnumerable`1 arguments)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.ValueConversion.NpgsqlArrayConverter`3.ArrayConversionExpression[TInput,TOutput,TConcreteOutput](LambdaExpression elementConversionExpression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.ValueConversion.NpgsqlArrayConverter`3..ctor(ValueConverter elementConverter)
         at InvokeStub_NpgsqlArrayConverter`3..ctor(Object, Span`1)
         at System.Reflection.MethodBaseInvoker.InvokeWithOneArg(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         --- End of inner exception stack trace ---
         at System.Reflection.MethodBaseInvoker.InvokeWithOneArg(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)
         at System.Activator.CreateInstance(Type type, Object[] args)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlArrayTypeMapping`3.CreateParameters(String storeType, RelationalTypeMapping elementMapping)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlArrayTypeMapping`3..ctor(String storeType, RelationalTypeMapping elementTypeMapping)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping.NpgsqlArrayTypeMapping`3..ctor(RelationalTypeMapping elementTypeMapping)
         at InvokeStub_NpgsqlArrayTypeMapping`3..ctor(Object, Span`1)
         at System.Reflection.MethodBaseInvoker.InvokeWithOneArg(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         --- End of inner exception stack trace ---
         at System.Reflection.MethodBaseInvoker.InvokeWithOneArg(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)
         at System.Activator.CreateInstance(Type type, Object[] args)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlTypeMappingSource.FindCollectionMapping(RelationalTypeMappingInfo info, Type modelType, Type providerType, CoreTypeMapping elementMapping)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.<>c.<FindMappingWithConversion>b__8_0(ValueTuple`4 k, RelationalTypeMappingSource self)
         at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd[TArg](TKey key, Func`3 valueFactory, TArg factoryArgument)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.FindMappingWithConversion(RelationalTypeMappingInfo mappingInfo, Type providerClrType, ValueConverter customConverter)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.FindMapping(Type type, IModel model, CoreTypeMapping elementMapping)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.<VisitChildren>g__VisitList|119_0[T](List`1 list, Boolean inPlace, Boolean& changed, <>c__DisplayClass119_0&)
         at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
         at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.SqlExpressions.ExistsExpression.VisitChildren(ExpressionVisitor visitor)
         at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlBinaryExpression.VisitChildren(ExpressionVisitor visitor)
         at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
         at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlTypeMappingPostprocessor.VisitExtension(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalTypeMappingPostprocessor.Process(Expression expression)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryTranslationPostprocessor.ProcessTypeMappings(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlQueryTranslationPostprocessor.Process(Expression query)
         at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
         at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
         at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)

Am I missing something?

[roji]

MissingMethodException almost always means that you're mixing major versions of EF and EFCore.PG - that's not supported. Align your EF package versions to the same major version, and if you're still encountering this, please post a minimal code repro.

[Trimetral]

I believe that versions are not mixed

    <PackageVersion Include="Microsoft.EntityFrameworkCore" Version="9.0.4" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Design" Version="9.0.4">
    <PackageVersion Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="9.0.4" />

MissingMethodException almost always means that you're mixing major versions of EF and EFCore.PG - that's not supported. Align your EF package versions to the same major version, and if you're still encountering this, please post a minimal code repro.

[Trimetral]

And I don't know what else I can post. There's really nothing special about this query
EF with DB first, scaffold has only 1 line with date column
entity.HasIndex(e => e.Date, "Idx_..Date");

data is DbSet.AsQueriable()

[Trimetral]

Interesting that this code is works for me and translates DateTime to timestamp

            var parameter = Expression.Parameter(typeof(MyClass), "x");
            var property = Expression.Property(parameter, nameof(MyClass.Date));
            var dateValues = filter.DatesEnd.Where(d => d.HasValue).Select(d => d!.Value).ToList();
            
            Expression? combinedExpression = null;

            foreach (var date in dateValues)
            {
                var dateConstant = Expression.Constant(date, typeof(DateTime?));
                var equality = Expression.Equal(property, dateConstant);
            
                combinedExpression = (combinedExpression == null) 
                    ? equality 
                    : Expression.OrElse(combinedExpression, equality);
            }

            if (filter.DatesEnd.Any(d => d == null))
            {
                var nullConstant = Expression.Constant(null, typeof(DateTime?));
                var isNullExpression = Expression.Equal(property, nullConstant);
            
                combinedExpression = (combinedExpression == null) 
                    ? isNullExpression 
                    : Expression.OrElse(combinedExpression, isNullExpression);
            }
            
            if (combinedExpression != null)
            {
                var lambda = Expression.Lambda<Func<MyClass, bool>>(combinedExpression, parameter);
                rewards = rewards.Where(lambda);
            }

And result
m."Date" IN (TIMESTAMPTZ '2025-12-01T00:00:00Z', TIMESTAMPTZ '2025-12-31T00:00:00Z')
or
m."Date" IN (TIMESTAMPTZ '2025-12-01T00:00:00Z', TIMESTAMPTZ '2025-12-31T00:00:00Z') OR m."Date" IS NULL