usesQueryComposition variable in .FromSql considers Recursive CTEs query to be stored procedures

[kglassmireacr]

https://github.com/aspnet/EntityFrameworkCore/blob/779d43731773d59ecd5f899a6330105879263cf3/src/EFCore.Relational/Query/ExpressionVisitors/RelationalEntityQueryableExpressionVisitor.cs#L191

I have a specific query that uses a recursive CTE. I noticed that due to the line above which just verifies that the query begins with variations of "SELECT ", this query is considered a stored procedure and eventually throws an exception here:

https://github.com/aspnet/EntityFrameworkCore/blob/779d43731773d59ecd5f899a6330105879263cf3/src/EFCore.Relational/Query/ExpressionVisitors/RelationalEntityQueryableExpressionVisitor.cs#L198

Are there any plans to make this check more robust? Or explicitly override the detection? I tried preceding the query with a dummy select

SELECT 1

to no avail.

Example code block:

            var sql =
@"
WITH RECURSIVE comment_tree AS
(
    SELECT  c.*, 0 as level, array[c.id] as path
    FROM    comments c
    WHERE   c.post_id = {0} and c.parent_id is null

    UNION ALL
    SELECT  cc.*, level + 1, comment_tree.path || cc.id
    FROM    comment_tree
    INNER JOIN    comments cc
    ON      cc.parent_id = comment_tree.id
    WHERE cc.post_id = {0}
)

SELECT *
FROM    comment_tree
order by comment_tree.path";
            var easyCommentSql = "select * from comments where comments.post_id = {0}";
            var commentsTree = _context
                .Comments
                .FromSql(sql, id)
                .Include(x => x.Post);

            
            return commentsTree.ToList();

If the easyCommentSql raw string is used, the functionality works wonderfully. However, if the recursive CTE query is used, it throws exception below:

System.InvalidOperationException
  HResult=0x80131509
  Message=The Include operation is not supported when calling a stored procedure.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitEntityQueryable(Type elementType)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.EntityQueryableExpressionVisitor.VisitConstant(ConstantExpression constantExpression)
   at System.Linq.Expressions.ConstantExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource, Boolean inProjection)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel)
   at Remotion.Linq.Clauses.MainFromClause.Accept(IQueryModelVisitor visitor, 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.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database, IDiagnosticsLogger`1 logger, Type contextType)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass13_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 Remotion.Linq.QueryableBase`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.IncludableQueryable`2.GetEnumerator()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ForumSiteCore.Business.Services.PostService.BestCommentsTree(Int64 id) in C:\Users\kglas\source\Repos\ForumSiteCore\ForumSiteCore\ForumSiteCore.Business\Services\PostService.cs:line 79
   at ForumSiteCore.Business.Services.PostService.Best(Int64 id) in C:\Users\kglas\source\Repos\ForumSiteCore\ForumSiteCore\ForumSiteCore.Business\Services\PostService.cs:line 84
   at ForumSiteCore.Web.Controllers.PostApiController.Best(Int64 id) in C:\Users\kglas\source\Repos\ForumSiteCore\ForumSiteCore\ForumSiteCore.Web\Controllers\PostApiController.cs:line 32
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()

[kglassmireacr]

This might be a duplicate of #4976. If so, I apologize.