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
- Fix to #23302 - Query: IQueryable in subquery sometimes triggers validation which prevents IQueryables in the final projection Problem was that during queryable method normalization we would validate... — committed to dotnet/efcore by maumar 3 years ago
- Adding more tests for #23302 - Query: IQueryable in subquery sometimes triggers validation which prevents IQueryables in the final projection select projecting queryable followed by SelectMany, Join,... — committed to dotnet/efcore by maumar 3 years ago
- Adding more tests for #23302 - Query: IQueryable in subquery sometimes triggers validation which prevents IQueryables in the final projection select projecting queryable followed by SelectMany, Join,... — committed to dotnet/efcore by maumar 3 years ago
- Adding more tests for #23302 - Query: IQueryable in subquery sometimes triggers validation which prevents IQueryables in the final projection select projecting queryable followed by SelectMany, Join,... — committed to dotnet/efcore by maumar 3 years ago
@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.
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.
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.
.NET Core 3.1 reached end of life 3 years after being released, as is the standard Long-Term Support policy - see these docs.
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.
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.