[WindowFunctions] Cannot execute query with framework-specific command ( e.g. ANY (Postgres))

[Wasenshi123]

When executing query that looks like this:

int[] unitList = [...] // some list, array, collection
query. Where(x => x.Units.Any(u => unitList.Contains(u.UnitId))); // any query that has .contains, or any thing that translate to ANY in postgres

it will throw exception like this: Unhandled expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression+ConcreteColumnExpression] Equal ANY([Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlParameterExpression])' of type 'Npgsql.EntityFrameworkCore.PostgreSQL.Query.Expressions.Internal.PostgresAnyExpression' encountered in 'SqlNullabilityProcessor'.

with Stack Trace like this:

at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitCustomSqlExpression(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
   at Zomp.EFCore.WindowFunctions.Query.Internal.WindowFunctionsSqlNullabilityProcessor.VisitCustomSqlExpression(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveColumnNullabilityInformation, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveColumnNullabilityInformation, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitExists(ExistsExpression existsExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveColumnNullabilityInformation, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveColumnNullabilityInformation, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Process(Expression queryExpression, IReadOnlyDictionary`2 parameterValues, Boolean& canCache)
   at Zomp.EFCore.WindowFunctions.Query.Internal.WindowRelationalParameterBasedSqlProcessor.ProcessSqlNullability(Expression queryExpression, IReadOnlyDictionary`2 parametersValues, Boolean& canCache)
   at Microsoft.EntityFrameworkCore.Query.RelationalParameterBasedSqlProcessor.Optimize(Expression queryExpression, IReadOnlyDictionary`2 parametersValues, Boolean& canCache)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate(IReadOnlyDictionary`2 parameters)
   at Microsoft.EntityFrameworkCore.Internal.RelationCommandCacheExtensions.RentAndPopulateRelationalCommand(RelationalCommandCache relationalCommandCache, RelationalQueryContext queryContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__22_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass28_0`2.<Execute>b__0(DbContext context, TState state)
   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.SplitQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.ToList()

I only tested with Postgres, but believe this affect some of SQL server also.

From my investigation, your extension lib missed some framework-specific classes for SqlNullabilityProcessor. They should be inherited from so that default functionality of each framework would stay intact. (SqlServerSqlNullabilityProcessor, NpgsqlSqlNullabilityProcessor)

Expected Behavior: Normal query should be intact when using the extension.

Current Behavior: Some normal queries are affected when using the extension.

[virzak]

Hi, will look at this at the weekend

[john-bartu]

Not only Any #12 . Same issue with SqlNullabilityProcessor .

[martinzima]

The fix is actually quite easy - I believe it should be enough to create a WindowFunctionsNpgsqlSqlNullabilityProcessor that would derive from from NpgsqlSqlNullabilityProcessor instead of the normal SqlNullabilityProcessor (already tried that in our fork and it seems to work). I might submit a PR later this week if I find the time.

[alexsxx]

@martinzima thank you! it works perfectly!

[virzak]

Fixed earlier in https://github.com/zompinc/efcore-extensions/commit/1af6e2c54e2b281befc7e4c6c9254f4188355f88.