No server-side handling of Contains (LIKE) on optional relationships

[kierenj]

Steps to reproduce

Complete repro project (uses mssqllocaldb):

https://github.com/RedRiverSoftware/ef-repros/tree/no-server-like-on-optional-relationship

(git clone, dotnet restore, ef database update, dotnet run)

By default RelationalEventId.QueryClientEvaluationWarning is set to throw. Remove line 13 of DemoContext.cs and it will instead log the details to the console.

The issue

I have a main entity and an optional child entity. When I try to filter with string.Contains on a property of the child entity (from the main entity), EF advises that it cannot process the query on the server.

Expected behaviour - something along the lines of using an INNER JOIN and LIKE when it's a single criterion, or an OUTER JOIN with LIKE or IS NULL test if there are further alternative filter criteria (i.e. ORed together).

Exception message: System.InvalidOperationException: Warning as error exception for warning 'RelationalEventId.QueryClientEvaluationWarning': The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To suppress this Exception use the DbContextOptionsBuilder.ConfigureWarnings API. ConfigureWarnings can be used when overriding the
 DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Stack trace:

   at Microsoft.EntityFrameworkCore.Infrastructure.SensitiveDataLogger`1.Microsoft.Extensions.Logging.ILogger.Log[TState](L
ogLevel logLevel, EventId eventId, TState state, Exception exception, Func`3 formatter)                                    
   at Microsoft.EntityFrameworkCore.Storage.RelationalLoggerExtensions.LogWarning(ILogger logger, RelationalEventId eventId
, Func`1 formatter)                                                                                                        
   at Remotion.Linq.QueryModelVisitorBase.VisitResultOperators(ObservableCollection`1 resultOperators, QueryModel queryMode
l)                                                                                                                         
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)                   
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)               
   at Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)       
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitSubQuery(SubQu
eryExpression expression)                                                                                                  
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression node)                  
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySour
ce querySource, Boolean inProjection)                                                                                      
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.CompileAdditionalFromClauseExpression(AdditionalFromC
lause additionalFromClause, QueryModel queryModel)                                                                         
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitAdditionalFromClause(AdditionalFromClause fromClause
, QueryModel queryModel, Int32 index)                                                                                      
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitAdditionalFromClause(AdditionalFromClause fromCl
ause, 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.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)       
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)

Further technical details

EF Core version: 1.0.0
Operating system: Windows 10
Visual Studio version: n/a commandline

[divega]

@anpete please confirm that this is a dupe of the general LEFT OUTER JOIN issue.

[kierenj]

@divega / @anpete - I'm getting many, many queries giving warnings around DefaultIfEmpty not being translatable. Would that potentially be this LEFT OUTER JOIN issue you refer to @divega? If so, might you have a link to that issue tracking that please? I'm on the nightlies from a few days ago but still getting the issues here. Thank you!

[anpete]

#4588