如何用整数序列左连接表？基于Entity Framework的实现方案

表结构定义 #

CREATE TABLE A (
    Id nvarchar(50) NOT NULL,
    ChildrenCount int NOT NULL
)
CREATE TABLE B (
    ParentId nvarchar(50) NOT NULL,
    I int NOT NULL,
    OtherColumns text NULL,
    AFlag bit NOT NULL
)

需求说明 #

表A中的ChildrenCount字段表示该父记录预期应存在的子记录总数，并非数据库中现有子记录的统计值。当前表B中部分子记录可能缺失，需要找出每个父记录对应的所有缺失子记录（即每个父记录应包含从0到ChildrenCount-1的所有I值，匹配不到表B记录的即为缺失）。

数据示例 #

表A数据 #

表B数据 #

期望查询结果 #

现有EF代码及问题 #

目前尝试的LINQ代码如下：

var sequenceQuery = "<SOME RAW SQL>";
var query = dbc.Set<A>()
    .SelectMany(a => dbc.Set<Int32Record>()
                        .FromSqlRaw(sequenceQuery)
                        .Where(i => i.Value < a.ChildrenCount)
                        .Select(i => new { a, i }))
    .GroupJoin(dbc.Set<B>(),
        x => new { ParentId = x.a.Id, I = x.i.Value },
        x => new { x.ParentId, x.I },
        (x, bs) => new { x.a, I = x.i.Value, Bs = bs.ToArray() })
    .SelectMany(x => x.Bs.DefaultIfEmpty(), (x, b) => new { A = x.a, x.I, B = b });

核心问题是如何在EF中生成整数序列，尝试了两种方案但都有缺陷：

• 方案1：SQL递归CTE

  var sequenceQuery = """
      WITH seq(i) AS (
          SELECT 0
          UNION ALL
          SELECT seq.i + 1 FROM seq WHERE (seq.i + 1) < 32767
      )
      SELECT seq.i FROM seq OPTION (MAXRECURSION 32767)
  """;
  

  问题：CTE的WITH子句需要包裹整个查询，无法直接通过FromSqlRaw使用。

• 方案2：T-SQL的generate_series函数

  var sequenceQuery = """
              SELECT value 
                FROM generate_series(0, (SELECT MAX(childrenCount) FROM A) - 1)
              """;
  

  生成的SQL如下：

  SELECT A.Id, s.I, B.*
    FROM A
    JOIN (SELECT value AS I FROM generate_series(0, (SELECT MAX(ChildrenCount) FROM A))) s 
      ON s.I < ChildrenCount
    LEFT JOIN B ON B.ParentId = A.Id AND B.I = s.I
   ORDER BY A.Id, s.I
  

  问题：能返回正确结果，但依赖数据库特定函数，一旦数据库变更（比如从SQL Server换为其他数据库）就会失效，希望找到不依赖数据库特性的通用解决方案。

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