efcore: Query: IQueryable in subquery sometimes triggers validation which prevents IQueryables in the final projection

query:

from l1 in ss.Set<Level1>()
                      orderby l1.Id
                      let inner = (from l2 in l1.OneToMany_Optional1
                                   where l2.Name != "Foo"
                                   let innerL1s = from innerL1 in ss.Set<Level1>()
                                                  where innerL1.OneToMany_Optional1.Any(innerL2 => innerL2.Id == l2.Id)
                                                  select innerL1.Name
                                   select innerL1s).FirstOrDefault()
                      select inner.ToList()

exception:

 The query contains a projection 'l1 => l1.OneToMany_Optional1
        .Where(l2 => l2.Name != "Foo")
        .Select(l2 => new { 
            l2 = l2, 
            innerL1s = DbSet<Level1>()
                .Where(innerL1 => innerL1.OneToMany_Optional1
                    .Any(innerL2 => innerL2.Id == l2.Id))
                .Select(innerL1 => innerL1.Name)
         })
        .Select(<>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.innerL1s)
        .FirstOrDefault()' of type 'IQueryable<string>'. Collections in the final projection must be an 'IEnumerable<T>' type such as 'List<T>'. Consider using 'ToList' or some other mechanism to convert the 'IQueryable<T>' or 'IOrderedEnumerable<T>' into an 'IEnumerable<T>'.
  Stack Trace: 
    QueryableMethodNormalizingExpressionVisitor.VerifyReturnType(Expression expression, ParameterExpression lambdaParameter) line 168
    QueryableMethodNormalizingExpressionVisitor.VerifyReturnType(Expression expression, ParameterExpression lambdaParameter) line 147
    QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) line 124
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
    ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
    QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) line 127
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    QueryTranslationPreprocessor.NormalizeQueryableMethod(Expression expression) line 84
    RelationalQueryTranslationPreprocessor.NormalizeQueryableMethod(Expression expression) line 45
    QueryTranslationPreprocessor.Process(Expression query) line 60
    RelationalQueryTranslationPreprocessor.Process(Expression query) line 54
    QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) line 174
    Database.CompileQuery[TResult](Expression query, Boolean async) line 72
    QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) line 114
    <>c__DisplayClass9_0`1.<Execute>b__0() line 98
    CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) line 78
    QueryCompiler.Execute[TResult](Expression query) line 94
    EntityQueryProvider.Execute[TResult](Expression expression) line 81
    EntityQueryable`1.GetEnumerator() line 93
    List`1.ctor(IEnumerable`1 collection)

About this issue

  • Original URL
  • State: open
  • Created 4 years ago
  • Reactions: 4
  • Comments: 26 (8 by maintainers)

Commits related to this issue

Most upvoted comments

@maumar can you elaborate on why it “doesn’t meet the bar”? This completely breaks existing behaviour, therefore makes update to the latest ef core version painful and risky. If it’s not the reason for patch I don’t know what is

@JoasE we decided this issue doesn’t meet the bar for a patch, sorry. The workaround is quite straightforward. The exception message indicates which subquery fails the validation (although it incorrectly states its a top level projection). Just need to add ToList call around that subquery and it should work.

@JoasE we decided this issue doesn’t meet the bar for a patch, sorry. The workaround is quite straightforward. The exception message indicates which subquery fails the validation (although it incorrectly states its a top level projection). Just need to add ToList call around that subquery and it should work.

Do you realize how many millions of lines of entity queries this change broke?

We only just now got far enough down fight though breaking changes to get mostly on to .Net 6.0 since .Net Core 3.1 is deprecated. We are looking at having to manually retest and possibility rewrite several years worth of work.

In our case, we could have several levels of sub-queries, depending on the pattern matching requirement in our application.

+mainrow
 + subquery1a
     +subquery1b 
 + subquery2a
     +subquery2b

I don’t mind programmatically patching queries (there are potentially thousands are not known by the developers, but user created), but for sure expect a sub-query to be within many of them.

in this case, joins will not work for us. for one, way to complex to convert and cant patch existing user created ones programmatically.

Is there a reason that the EF Framework way was removed or broken for EF Core?

Can we write some kind of Visitor pattern to intercept this, and instead of throwing the exception, extending the SQL somehow? Just kinda shocked that this was removed from EFCore. I can’t seem to find out why it was though, unless it’s just a bug.

These new validations that do not occur until runtime are preventing entire applications from being upgraded from .Net Core 3.1 (which I remind you was deprecated by Microsoft after only two years.)

.NET Core 3.1 reached end of life 3 years after being released, as is the standard Long-Term Support policy - see these docs.

Also, fundamental, runtime only breaking changes can we expect in the future?

We try hard to avoid breaking changes in general, both ones which show up at compilation and those which show up at runtime only. However, we do introduce these in some cases, when we believe they’re essential to the product, and benefit our user community more than the work they create. Without discussing this particular breaking change, requiring a product to never make any breaking changes effectively condemns it to stagnate.

These runtime errors make it impossible to find these new “bugs” without manually testing the entire platforms.

We strongly recommend investing in a robust automated test suite; sufficient coverage should detect the failing queries immediately and help you apply the trivial workaround to them. Even if we never purposefully introduced breaking changes into the product, unintentional ones may find their way in, and a good test suite is the only way for users to be sure that their application continues working across upgrades.