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

[maumar]

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)

[maumar]

related to #23205 and #16314

[JoasE]

I experienced the same problem with the query below:

var defaultLanguageId = 1;
var selectedLanguageId = 2;

var query = from defaultTranslation in context.Translations
            where defaultTranslation.LanguageId == defaultLanguageId
            let translation = from translation in context.Translations
                                where translation.LanguageId == selectedLanguageId &&
                                    translation.Key == defaultTranslation.Key
                                select translation
            from selectedTranslation in translation.DefaultIfEmpty()
            select selectedTranslation ?? defaultTranslation;
var translations = originalQuery.AsNoTracking().ToList();

A possible workaround would be:

var query = from defaultTranslation in context.Translations
            where defaultTranslation.LanguageId == defaultLanguageId
            join selectedTranslation in context.Translations.Where(x => x.LanguageId == selectedLanguageId)
                on defaultTranslation.Key equals selectedTranslation.Key into ts
            from selectedTranslation in ts.DefaultIfEmpty()
            select selectedTranslation ?? defaultTranslation;

var translations = query.AsNoTracking().ToList();

This will result in the same SQL being executed using the SqlServer database provider 5.0.3

[JoasE]

@ajcvickers @maumar This issue is currently (part of) the reason my company is delaying the update to EF Core 5 for one of our projects. It seems like something which should be working in EF Core 5 (aswell as in 6). Is there any way this could be added to the 5.x milestone?

[ajcvickers]

@maumar to investigate patch

[maumar]

@JoasE easier workaround: add ToList() at the end of subquery defined in the "let":

var defaultLanguageId = 1;
var selectedLanguageId = 2;

var query = from defaultTranslation in context.Translations
            where defaultTranslation.LanguageId == defaultLanguageId
            let translation = (from translation in context.Translations
                                where translation.LanguageId == selectedLanguageId &&
                                    translation.Key == defaultTranslation.Key
                                select translation).ToList()
            from selectedTranslation in translation.DefaultIfEmpty()
            select selectedTranslation ?? defaultTranslation;
var translations = originalQuery.AsNoTracking().ToList();