SqlServer Provider: Client Evaluation when comparing with Nullable value

[MovGP0]

I want to have an conditional filter dependent if a property of an entity projection is null or not null. For performance reasons this needs to be executed on the database, because i get way to many data and it takes too long if the query runs in memory.

I've tried different approaches to make this work, but none seem to work. Since I consider this example trivial in SQL, I didn't expect it not to work.

Steps to reproduce

Given I have two datasets, Foo and Bar, and also the following projection:

public sealed class SomeProjection
{
    public Guid? FooId { get; set; }
    public Guid BarId { get; set; }
}

Version 1

var result = await (
    from f in Context.Foo
    from b in Context.Bar
    where f.Id != null || b.Id == f.Id
    select new SomeProjection { FooId = f.Id, BarId = b.Id }
)
.AsNoTracking()
.ToListAsync(cancellationToken)
.ConfigureAwait(false); 

Version 2

public static IQueryable<SomeProjection> FilterByFoo(IQueryable<SomeProjection> items)
{
    return items.Where(e => e.FooId == null || e.FooId == e.BarId);
}

var result = await (
    from f in Context.Foo
    from b in Context.Bar
    select new SomeProjection { FooId = f.Id, BarId = b.Id }
)
.FilterByFoo()
.AsNoTracking()
.ToListAsync(cancellationToken)
.ConfigureAwait(false); 

Version 3

Alternative way of expressing it as explicit Expression tree:

public static IQueryable<SomeProjection> FilterByFoo(IQueryable<SomeProjection> items)
{
            var item = Expression.Parameter(typeof(SomeProjection), "item");
            var fooId = Expression.Property(item, "FooId");
            var barId = Expression.Property(item, "BarId");
            var @null = Expression.Constant(null);
            var isNull = Expression.Equal(fooId, @null);
            var equals = Expression.Equal(Expression.Convert(fooId, typeof(Guid)), barId);
            var orElse = Expression.OrElse(isNull, equals);
            var lambda = Expression.Lambda<Func<SomeProjection, bool>>(orElse, item);
            return items.Where(lambda);
}

Exception

Exception message: Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning
Stack trace: 
   at Microsoft.EntityFrameworkCore.Diagnostics.EventDefinition`1.Log[TLoggerCategory](IDiagnosticsLogger`1 logger, WarningBehavior warningBehavior, TParam arg, Exception exception)
   at Microsoft.EntityFrameworkCore.Internal.RelationalLoggerExtensions.QueryClientEvaluationWarning(IDiagnosticsLogger`1 diagnostics, QueryModel queryModel, Object queryModelElement)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.WarnClientEval(QueryModel queryModel, Object queryModelElement)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.Clauses.WhereClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileAsyncQuery[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass22_0`1.<CompileAsyncQuery>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileAsyncQuery[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.System.Collections.Generic.IAsyncEnumerable<TResult>.GetEnumerator()
   at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken)

Further technical details

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

[MovGP0]

See also: https://github.com/aspnet/EntityFrameworkCore/issues/12046#issuecomment-410863108

[maumar]

this is fixed in current bits