Projection filtering (Select().Where()) could not be translated on 2nd level

[Reris]

I'm currently upgrading my little API helper Riql to standard2.1/core3.1. Everything is fine, except efcore since 3.0.

If I try to reduce the transferred data with a select-projection, it works fine unless the where-part tries to compare compare a second level property (or any deeper)

There are NO external function calls nor anything else which should run locally - the exception only happens with inner projections with a following Where() statement.

Steps to reproduce

Reproduction code is available on Riql:
Test Apply_WhereProjectionLevel1_ShouldFilterProjection works:

var projection = orders.Select(o => new {o.Id, o.Requirement, o.Manager});
var expected = (from p in projection where p.Manager != null && p.Manager.RoomNr == 105 select p).ToList();

Test Apply_WhereProjectionLevel2_ShouldFilterProjection causes the error:

var projection = orders.Select(
     o => new
     {
          o.Id,
          o.Requirement,
          ManagerInfo = o.Manager != null
               ? new {o.Manager.FirstName, Room = o.Manager.RoomNr}
               : null
          });
var expected = (from p in projection where p.ManagerInfo != null && p.ManagerInfo.Room == 105 select p).ToList();

The highlighted part is: p.ManagerInfo.Room == 105, accessing the second level of the projection.
The same happens with strict projection types / non-anonymous projections.

All versions prior to efcore 3 simply translated the projection to an inner selection and everything ran serverside.

Got Exception:

System.InvalidOperationException: The LINQ expression 'DbSet<Order>
    .LeftJoin(
        outer: DbSet<Employee>, 
        inner: o => EF.Property<Nullable<Guid>>(o, "ManagerId"), 
        outerKeySelector: e => EF.Property<Nullable<Guid>>(e, "Id"), 
        innerKeySelector: (o, i) => new TransparentIdentifier<Order, Employee>(
            Outer = o, 
            Inner = i
        ))
    .Where(o => EF.Property<Nullable<Guid>>(o.Inner, "Id") != null ? new { 
        FirstName = o.Inner.FirstName, 
        Room = o.Inner.RoomNr
     } : null != null && EF.Property<Nullable<Guid>>(o.Inner, "Id") != null ? new { 
        FirstName = o.Inner.FirstName, 
        Room = o.Inner.RoomNr
     } : null.Room == 105)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Riql.Tests.RiqlComplex_Tests.Apply_WhereProjectionLevel2_ShouldFilterProjection(IQueryable`1 orders, IDisposable lifetime) in D:\Riql\tests\Riql.Tests\RiqlComplex_Tests.cs:line 328

Further technical details

EF Core version: 3.1
Database provider: Sqlite, Postgres
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.5.4

Edit: Formatting, Added missing line in 1st example

[MintPlayer]

This is because efcore is now blocking implicit execution of client-side LINQ-queries.

When a LINQ query is being evaluated against a DbSet, EF Core tries to convert the entire LINQ tree to an SQL query. In efcore 2.2, when a part of the LINQ query could not be converted to SQL, the conversion stops and the query is being translated to sql early on. This caused performance issues on large databases (millions of records) to remain undetected.

Your linq query:

var expected = orders
    .Select(o => new {
        o.Id,
        o.Requirement,
        ManagerInfo = o.Manager != null
            ? new {o.Manager.FirstName, Room = o.Manager.RoomNr}
            : null
    })
    .Where(p => p.ManagerInfo != null && p.ManagerInfo.Room == 105)
    .ToList();

is not entirely convertible to SQL, since in your Select-clause you're converting all items to anonymous objects. When the result for the LINQ query is being yielded, efcore detects that it cannot translate the Select-clause and therefor stops the conversion, throwing the exception stating that the LINQ query cannot be translated.

This prevents performance issues from going undetected at development stage.

Try following code instead:

var expected = orders
    .Where(o => o.Manager != null && o.Manager.RoomNr == 105)
    .Select(o => new {
        o.Id,
        o.Requirement,
        ManagerInfo = o.Manager != null
            ? new {o.Manager.FirstName, Room = o.Manager.RoomNr}
            : null
    })
    .ToList();

EF Core allows untranslatable LINQ-statements up to 1 level (just enough to convert entities to dtos).

In my opinion, this issue can be closed.

[Reris]

There's no part which denies a translation to a simple SQL-92 query - because it's just a projection which renames fields of entities to a reduced subset.
This issue has nothing to do with the goal of the major change from v2 to v3 which prevents local executioning.
And there is no perfomance increase in this limitation, as it enforces you to load data which you wouldnt need anyways.

Level 1 projections are working, right. EF6 and EFCore2 where able to do level2 projections (and more) via SQL without any local transformation.

EF3 allows this too - only the following 'WHERE'-limitation breaks.
Just to clarify: Everything works well regardless how deep, be it concrete or anonymous DTOs - as long as there's no following WHERE.

So, in my opinion, this is an issue which shouldnt be closed, because its a major drawback limitation in how we design a DTO (flat vs complex) only to get the 'WHERE' part of a query working.

[joakimriedel]

Perhaps related second level null checking: #13220

Error message in 3.1 seems similar, would be interesting for you to try 5.0 preview and see if the error message changes similarly.

[joakimriedel]

Might also be related: #12148

Seems like strange things happen when null checking in anonymous objects - @smitpatel might know why and if there is any improvements planned in this field for 5.0 - still holding my thumbs for a query translator that works with more complex queries.

[Reris]

Same exception in 5.0.0-preview.3.20181.2

[Reris]

Okay, i figured it out how it ran on efcore2 (sqlite):

SELECT "o"."ManagerId", "o.Manager"."FirstName", "o.Manager"."RoomNr", "o"."Id", "o"."Requirement", CASE
          WHEN "o"."ManagerId" IS NOT NULL
          THEN 1 ELSE 0
      END, "o.Manager"."RoomNr" AS "Room"
      FROM "Orders" AS "o"
      LEFT JOIN "Employees" AS "o.Manager" ON "o"."ManagerId" = "o.Manager"."Id"
warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'where ((IIF((Property([o], "ManagerId") != null), new <>f__AnonymousType2`2(FirstName = [o.Manager]?.FirstName, Room = Convert([o.Manager]?.RoomNr, Int32)), null) != null) AndAlso (IIF((Property([o], "ManagerId") != null), new <>f__AnonymousType2`2(FirstName = [o.Manager]?.FirstName, Room = Convert([o.Manager]?.RoomNr, Int32)), null).Room == 105))' could not be translated and will be evaluated locally.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

So basically we've got everything to do a WHERE in the server query, but unfortuenatly it was executed clientside. :(
So this seems to be a feature in ef6 which hasnt been implemented yet.

[joakimriedel]

OK, perhaps not the same root cause then. But still, seems like null propagating in dto has some issues to be worked out in different ways regarding anonymous objects (dto).

[Reris]

No, it seems more like a visitor mismatch while generating/translating the expression (o.Inner).

Same error with concrete types

public class ConreteOrder
{
    public int Id { get; set; }
    public String? Requirement { get; set; }
    public ConreteEmployee? ManagerInfo { get; set; }
}

public class ConreteEmployee
{
    public String? FirstName { get; set; }
    public int Room { get; set; }
}
...

var projection = orders.Select(
    o => new ConreteOrder
    {
        Id = o.Id,
        Requirement = o.Requirement,
        ManagerInfo = o.Manager != null
                          ? new ConreteEmployee { FirstName = o.Manager.FirstName, Room = o.Manager.RoomNr}
                          : null
    });
var expected = (from p in projection where p.ManagerInfo != null && p.ManagerInfo.Room == 105 select p).ToList();

System.InvalidOperationException: The LINQ expression 'DbSet<Order>
    .LeftJoin(
        outer: DbSet<Employee>, 
        inner: o => EF.Property<Nullable<Guid>>(o, "ManagerId"), 
        outerKeySelector: e => EF.Property<Nullable<Guid>>(e, "Id"), 
        innerKeySelector: (o, i) => new TransparentIdentifier<Order, Employee>(
            Outer = o, 
            Inner = i
        ))
    .Where(o => EF.Property<Nullable<Guid>>(o.Inner, "Id") != null ? new ConreteEmployee{ 
        FirstName = o.Inner.FirstName, 
        Room = o.Inner.RoomNr 
    }
     : null != null && EF.Property<Nullable<Guid>>(o.Inner, "Id") != null ? new ConreteEmployee{ 
        FirstName = o.Inner.FirstName, 
        Room = o.Inner.RoomNr 
    }
     : null.Room == 105)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Riql.Tests.RiqlComplex_Tests.Apply_WhereProjectionLevel2_ShouldFilterProjection(IQueryable`1 orders, IDisposable lifetime) in 

even without nullability checks (which wouldnt run clientside either):

var projection = orders.Select(
    o => new ConreteOrder
    {
        Id = o.Id,
        Requirement = o.Requirement,
        ManagerInfo = o.Manager = new ConreteEmployee { FirstName = o.Manager.FirstName, Room = o.Manager.RoomNr}
    });
var expected = (from p in projection where p.ManagerInfo.Room == 105 select p).ToList();

System.InvalidOperationException: The LINQ expression 'DbSet<Order>
    .LeftJoin(
        outer: DbSet<Employee>, 
        inner: o => EF.Property<Nullable<Guid>>(o, "ManagerId"), 
        outerKeySelector: e => EF.Property<Nullable<Guid>>(e, "Id"), 
        innerKeySelector: (o, i) => new TransparentIdentifier<Order, Employee>(
            Outer = o, 
            Inner = i
        ))
    .Where(o => new ConreteEmployee{ 
        FirstName = o.Inner.FirstName, 
        Room = o.Inner.RoomNr 
    }
     != null && o.Inner.RoomNr == 105)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Riql.Tests.RiqlComplex_Tests.Apply_WhereProjectionLevel2_ShouldFilterProjection(IQueryable`1 orders, IDisposable lifetime) in 

[HEBOS]

The only workaround is not using ProjectTo, but doing ToList() first, and after that issuing AutoMapper.Map(...)

[Reris]

Sure, this would be a workaround, but it would load/deserialize tons of needless data, because the WHERE runs clientside.
The other workaround would be to flatten the transformation down to a flat projection (in ex: with an anonymous type) and retransform it after .ToList() via .Select().

Again, this only happens with follow-up operations when the follow-up accesses a child property when the child is a transformation and not a plain entity. So I think it's a bug in the TranslatingExpressionVisitor which provides the wrong inner expression.

Btw: sorry for the typo in Con_c_rete :)

[HEBOS]

Not really @Reris .
You can issue Where before ToList, and then do the mapping on the result (your final subset of data).
This adds an overhead, as mapping is not executed on server, but on client.
However, this is not applicable to all use cases (line mine).

Please fix that guys, as this is a blocker for anyone who uses optional relationships.

[binsys]

this has been fixed in 7c3a117

Originally posted by @maumar in #14321 (comment)

EF CORE 3.X workaround (JUST A WORKAROUND !!!):

copy QueryOptimizingExpressionVisitor and some private or internal method to your codebase.

QueryOptimizingExpressionVisitor

override your DbContext class OnConfiguring method,

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.EnableDetailedErrors(true);
            optionsBuilder.EnableSensitiveDataLogging(true);

            {
                optionsBuilder
                    .ReplaceService<IQueryTranslationPreprocessorFactory, MyQueryTranslationPreprocessorFactory>();

            }

            base.OnConfiguring(optionsBuilder);
        }

public class MyQueryTranslationPreprocessorFactory : RelationalQueryTranslationPreprocessorFactory
    {
        private readonly QueryTranslationPreprocessorDependencies _dependencies;
        private readonly RelationalQueryTranslationPreprocessorDependencies _relationalDependencies;
        public MyQueryTranslationPreprocessorFactory(
            QueryTranslationPreprocessorDependencies dependencies, 
            RelationalQueryTranslationPreprocessorDependencies relationalDependencies) 
            : base(dependencies, relationalDependencies)
        {
            _dependencies = dependencies;
            _relationalDependencies = relationalDependencies;
        }

        public override QueryTranslationPreprocessor Create(QueryCompilationContext queryCompilationContext)
        {
            return new MyRelationalQueryTranslationPreprocessor(_dependencies, _relationalDependencies, queryCompilationContext);
        }
    }

#pragma warning disable EF1001 // Internal EF Core API usage.
    public class MyRelationalQueryTranslationPreprocessor : RelationalQueryTranslationPreprocessor
    {
        private readonly QueryCompilationContext _queryCompilationContext;
        public MyRelationalQueryTranslationPreprocessor(
            QueryTranslationPreprocessorDependencies dependencies,
            RelationalQueryTranslationPreprocessorDependencies relationalDependencies,
            QueryCompilationContext queryCompilationContext
            )
            : base(dependencies, relationalDependencies, queryCompilationContext)
        {
            _queryCompilationContext = queryCompilationContext;
        }

        public override Expression Process(Expression query)
        {
            query = new EnumerableToQueryableMethodConvertingExpressionVisitor().Visit(query);

            query = new QueryMetadataExtractingExpressionVisitor(_queryCompilationContext).Visit(query);

            query = new InvocationExpressionRemovingExpressionVisitor().Visit(query);

            query = new AllAnyToContainsRewritingExpressionVisitor().Visit(query);


            query = new GroupJoinFlatteningExpressionVisitor().Visit(query);


            query = new NullCheckRemovingExpressionVisitor().Visit(query);

            query = new EntityEqualityRewritingExpressionVisitor(_queryCompilationContext).Rewrite(query);

            query = new SubqueryMemberPushdownExpressionVisitor().Visit(query);

            query = new NavigationExpandingExpressionVisitor(_queryCompilationContext,
                Dependencies.EvaluatableExpressionFilter).Expand(query);

            // debug output expression before
            {
                var expressionPrinter = new ExpressionPrinter();
                var debugStr = expressionPrinter.PrintDebug(query);
                Debug.WriteLine(debugStr);
            }

            query = new QueryOptimizingExpressionVisitor().Visit(query);
            query = new NullCheckRemovingExpressionVisitor().Visit(query);

            // debug output expression after
            {
                var expressionPrinter = new ExpressionPrinter();
                var debugStr = expressionPrinter.PrintDebug(query);
                Debug.WriteLine(debugStr);
            }

            query = new FunctionPreprocessingExpressionVisitor().Visit(query);

            new EnumerableVerifyingExpressionVisitor().Visit(query);
            return query;
        }



        private class EnumerableVerifyingExpressionVisitor : ExpressionVisitor
        {
            protected override Expression VisitMethodCall(MethodCallExpression node)
            {
                if (node.Method.DeclaringType == typeof(Enumerable) && node.Arguments[0].Type.IsGenericType && node.Arguments[0].Type.GetGenericTypeDefinition() == typeof(IQueryable<>) && !string.Equals(node.Method.Name, "ToList") && !string.Equals(node.Method.Name, "ToArray"))
                {
                    throw new InvalidFilterCriteriaException();
                }
                return base.VisitMethodCall(node);
            }
        }
    }
#pragma warning restore EF1001 // Internal EF Core API usage.

[smitpatel]

Duplicate of #20711