FromSqlRaw not working for me in .net 6

[MohammadSLM]

I have a query that is working on .net 3
but after I upgraded to .net 6 isn't working anymore
I test the query in SQL and its working but it can't be translated into Linq and give me this exception:
'FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it.
Consider calling 'AsEnumerable' after the method to perform the composition on the client side.

make it AsEnumerable doesn't fix it.

            string sqlCheckAllParents = @";WITH cte AS 
(
    SELECT Id, ParentId, Published, IsDeleted 
    FROM Comments h 
    WHERE Id = {0} 
    UNION ALL
    SELECT p.Id, p.ParentId, p.Published, p.IsDeleted 
    FROM
        Comments p 
        INNER JOIN
            cte c ON c.parentId = p.Id 
)
select Id from cte m where IsDeleted = 1 or Published = 0";

            var dontAllowLoadData = await _comments.FromSqlRaw(sqlCheckAllParents, parentId ?? 0).Select(x => x.Id).ToListAsync().ConfigureAwait(false);
            if (dontAllowLoadData.Any())
            {
                return new DataGridViewModel<Comment>
                {
                    Records = new List<Comment>(),
                    TotalCount = 0
                };
            }

EF Core version:
Database provider: (Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (.NET 6.0)

[bricelam]

Duplicate of #4976