我的T-SQL查询为何不可组合，无法兼容EF Core的SqlQuery？

我正在编写用于EF Core的T-SQL查询，但收到“查询不可组合”的错误。搜索相关内容没找到易懂的信息。

遇到的C#代码错误如下：

System.InvalidOperationException: 'FromSql' or 'SqlQuery' 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.

我的疑问：
以下我的查询中哪些因素导致其不可组合？是因为使用了CTEs吗？该查询未包含任何参数。

WITH CollectionInformation AS (
  SELECT
    c.Id AS CollectionId,
    c.Name AS CollectionName,
    p.Name AS ProjectName,
    t.Id AS TaxonomyId,
    t.Name AS TaxonomyName,
    c.DateCreated
  FROM Collections AS c
  JOIN Projects AS p ON p.Id = c.PrimaryProjectId
  JOIN Taxonomies AS t ON t.Id = c.TaxonomyId
),
SessionCounts AS (
  SELECT
    s.CollectionId,
    COUNT(s.Id) AS SessionCount
  FROM Sessions AS s
  JOIN CollectionInformation AS c ON c.CollectionId = s.CollectionId
  GROUP BY s.CollectionId
),
BoxCount AS (
  SELECT
    c.CollectionId,
    COUNT(z.Id) AS ZipfileCount
  FROM BoxZipfiles AS z
  JOIN Sessions AS s ON s.Id = z.SessionId
  JOIN CollectionInformation AS c ON c.CollectionId = s.CollectionId
  GROUP BY c.CollectionId
)
SELECT c.*, s.SessionCount, b.ZipfileCount
FROM CollectionInformation AS c
JOIN SessionCounts AS s ON s.CollectionId = c.CollectionId
JOIN BoxCount AS b ON b.CollectionId = c.CollectionId

原因分析 #

CTEs本身不是导致查询不可组合的原因，EF Core判定查询不可组合的核心依据是：SQL是否能被EF Core作为子查询嵌入到另一个SQL语句中。

你的查询被标记为不可组合，主要有两个关键点：

• 多层依赖的CTE结构：SessionCounts和BoxCount都依赖于CollectionInformation，这种嵌套依赖的CTE无法被EF Core无缝嵌入到后续的组合查询（比如调用FromSql/SqlQuery后添加Where、OrderBy等LINQ操作）中。
• 复杂的整体结构：EF Core要求可组合SQL是能被当作子查询的简单SELECT结构，而你的多CTE查询整体逻辑嵌套过多，EF Core无法将其拆解并与后续LINQ生成的SQL合并。

即使没有参数，只要SQL结构不符合EF Core的可组合要求，就会触发该错误。

解决办法 #

• 若需继续在EF Core中组合查询：将CTE逻辑改写为子查询形式，或用关联子查询、窗口函数把统计逻辑合并到主查询中，让整体SQL成为单一的SELECT结构。
• 若无需后续组合：在调用FromSql/SqlQuery后立即添加AsEnumerable()，让后续操作在客户端内存中执行。

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