Query: Error using a compare with a nullable boolean (again)

[eloekset]

I'm using EFCore with OData v4 and execute a query to filter by a string property. When OData applies the filter criteria, an exception is thrown by EFCore.

Interesting finds:

• Filtering by a boolean value or a date value works just fine.
• Filtering by a string value works fine with EF 6.1 and EFCore.InMemory, but not with EFCore.SqlServer.

The error is similar to the one reported in issue #5877, but that issue is resolved and fixed in an earlier verison of EFCore.

This is the exception that occurs:

Exception message: The binary operator Equal is not defined for the types 'System.Nullable`1[System.Boolean]' and 'System.Boolean'.
Stack trace:
at System.Linq.Expressions.Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)   
at System.Linq.Expressions.Expression.Equal(Expression left, Expression right, Boolean liftToNull, MethodInfo method)   
at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method, LambdaExpression conversion)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.SearchConditionTranslatingVisitor.VisitConditional(ConditionalExpression expression)   
at System.Linq.Expressions.ConditionalExpression.Accept(ExpressionVisitor visitor)   
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)   
at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.SearchConditionTranslatingVisitor.VisitUnary(UnaryExpression expression)   
at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)   
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.ApplyOptimizations(Expression expression, Boolean searchCondition, Boolean joinCondition)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)   
at Microsoft.EntityFrameworkCore.Query.Expressions.SelectExpression.Accept(ExpressionVisitor visitor)   
at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.<VisitJoin>b__36_0(Expression e)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.VisitJoin[T](IReadOnlyList`1 items, Action`1 itemAction, Action`1 joinAction)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.VisitJoin(IReadOnlyList`1 expressions, Action`1 joinAction)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)   
at Microsoft.EntityFrameworkCore.Query.Expressions.SelectExpression.Accept(ExpressionVisitor visitor)   
at Remotion.Linq.Parsing.ThrowingExpressionVisitor.Visit(Expression expression)   
at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.GenerateSql(IReadOnlyDictionary`2 parameterValues)   
at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.GetRelationalCommand(IReadOnlyDictionary`2 parameters)   
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)   
at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)   
at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, TState state)   
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()   
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()   
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext()   
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()   
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)   
at System.Web.OData.Query.TruncatedCollection`1..ctor(IQueryable`1 source, Int32 pageSize)   
at System.Web.OData.Query.ODataQueryOptions.LimitResults[T](IQueryable`1 queryable, Int32 limit, Boolean& resultsLimited)

Steps to reproduce

I've reproduced the error in a sample project.

• This is the branch where the error occurs: https://github.com/eloekset/EfCoreODataQueryBug/tree/EfCoreSqlServer
• This is a branch using EFCore.InMemory, where it works fine: https://github.com/eloekset/EfCoreODataQueryBug/tree/EfCore
• This is a branch using EF 6.1, where it works fine: https://github.com/eloekset/EfCoreODataQueryBug/tree/Ef6

To run the sample project, create a DB and run the CreateDb.sql script: https://raw.githubusercontent.com/eloekset/EfCoreODataQueryBug/EfCoreSqlServer/EfCoreODataQueryBug.Web/Data/CreateDb.sql

Then start debugging in Visual Studio and type something in the filter row for the Name column of the grid. The error with full details is returned in the response from the OData API:

Same filter applied when running the Ef6 branch:

...and when running the EfCore branch (which uses the InMemory provider):

Filtering by the date and boolean columns of the grid works just fine for all three versions.

Further technical details

EF Core version: 1.1.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017

[smitpatel]

While this is similar to #5877, still different kind of query pattern is causing same error. The code where it is throwing error was rewritten to fix bugs like this in #7554 which was part of 2.0.0-preview1 release.

Can you please try running the code with 2.0.0-preview1 package and see if it fixes the issue?

[smitpatel]

Dupe of #8065

This was bug in EF Core which was fixed in #7554. It was arising because ODATA introduced null propagation and that query pattern was faulty in EF Core. Upgrading EF Core to 2.0.0-preview1 or later will fix the issue.

Or you can use the work-around as posted in duplicated issue.

Modify ODataController's EnableQueryAttribute, by including:

HandleNullPropagation = HandleNullPropagationOption.False

I couldn't upgrade the app to EF 2 packages. I verified that work-around works correctly for this case.