EF Core中通过原始SQL参数化设置CTE最大递归深度失效问题

无法通过带数据库参数化的原始SQL设置CTE最大递归深度值。

使用MySQL数据库开发多个用到递归CTE的程序时，尝试在代码中通过带数据库参数的原始SQL设置CTE最大递归深度值，功能未按预期生效。参考MySQL 8.0官方文档关于递归CTE递归限制的说明，在CTE查询中配置最大递归值，使用的代码如下：

var cteString = @"WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = {0}) */ * FROM cte";

result = await this.DbContext
                        .Set<TEntity>()
                        .FromSqlRaw(cteString, new object[] { 10 })
                        .CountAsync(cancelToken) > 0; 

该写法运行无报错，但实际使用的是默认cte_max_recursion_depth配置，参数传入的值未生效。如果将最大递归深度值硬编码在SQL中，或使用插值字符串传值时功能可正常运行。需要找到符合参数化写法规范的可行解决方案。

相关环境信息 #

• EF Core版本：6.0.2
• 数据库提供程序：Pomelo.EntityFrameworkCore.MySql
• 目标框架：.NET 6.0
• 开发IDE：Visual Studio 2022 17.0.3

MySQL的SET_VAR()优化器提示仅支持字面量值，不支持参数占位符。SQL解析阶段，优化器会在参数替换逻辑执行前先读取优化器提示内的配置，因此写在提示中的{0}占位符不会被替换为实际传入的参数值，最终配置不生效。

方案1：会话级预设置变量（推荐，无注入风险） #

不在SET_VAR提示中传参，而是在执行CTE查询前，先通过参数化SQL设置当前会话的cte_max_recursion_depth变量，该配置仅对当前数据库连接生效，不会影响其他连接。

// 参数化设置当前会话的CTE最大递归深度
await this.DbContext.Database.ExecuteSqlRawAsync(
    "SET SESSION cte_max_recursion_depth = {0}", 
    new object[] { 10 }, 
    cancelToken);

var cteString = @"WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte";

result = await this.DbContext
                        .Set<TEntity>()
                        .FromSqlRaw(cteString)
                        .CountAsync(cancelToken) > 0;

// 可选：执行完成后恢复默认配置
await this.DbContext.Database.ExecuteSqlRawAsync(
    "SET SESSION cte_max_recursion_depth = DEFAULT;", 
    cancelToken);

该方案全程使用EF Core的参数化传参能力，不存在SQL注入风险，完全符合安全编码规范。

方案2：白名单校验后拼接字面量（适用于固定取值场景） #

如果必须在单条SQL内通过SET_VAR提示完成配置，且递归深度的取值范围是固定的可枚举值（比如仅允许10、100、1000三个档位），可以先对传入值做严格白名单校验，校验通过后再将字面量拼接到SQL的提示位置。
注意：禁止直接拼接未经过校验的外部传入值，否则会引入SQL注入风险。

内容的提问来源于stack exchange，提问作者Ragi Dayananda