嵌套SelectMany中DefaultIfEmpty工作异常的SQL生成问题

需求 #

获取指定项的所有关联子项，并按关联的TestQuery结果降序排序。

在.NET 6/8环境下，使用嵌套SelectMany并结合DefaultIfEmpty时，生成的SQL不符合预期：

原查询代码 #

DbSet<MyRecord<long>>().FromSql("select * from unnest({0})", new object[] { NpgsqlParameter<MyRecord<long>[]>() })
.SelectMany(pk => dataContext.TestItems
    .Where(item => pk.Item == item.ParentId)
    .SelectMany(
        collectionSelector: entity => dataContext.TestQueries
            .Where(a => a.QueryId == entity.EntityId)
            .DefaultIfEmpty(), 
        resultSelector: (left, right) => new JoinResult<TestItem, TestQuery>{ 
            Left = left, 
            Right = right 
        }
    )
    .OrderByDescending(result => result.Right.ObjectId)
    .Select(result => result.Left))

实体类定义 #

class MyRecord<T>
{
    public T Item {get;set;}
}
class JoinResult<T1, T2>
{
    public T1 Left { get; set; }
    public T2 Right { get; set; }
}
class TestItem
{
    public int EntityId {get;set;}
    public string Name {get;set;}
    public int ParentId {get;set;} // 关联TestItem.EntityId
}
class TestQuery
{
    public int QueryId {get;set;}
}

实际生成的SQL #

SELECT t0.EntityId, t0.Name, t0.ParentId
FROM (
    select * from unnest(ARRAY[101,102])
) AS e
LEFT JOIN LATERAL (
    SELECT c.EntityId, c.Name, c.ParentId
    FROM items AS c
    JOIN LATERAL (
        SELECT s.QueryId
        FROM queries AS q
        WHERE q.QueryId = c.EntityId
    ) AS t ON TRUE
    WHERE e.unnest = c.ParentId
    ORDER BY t.QueryId DESC
) AS t0 ON TRUE

预期生成的SQL #

SELECT t0.EntityId, t0.Name, t0.ParentId
FROM (
    select * from unnest(ARRAY[101,102])
) AS e
JOIN LATERAL (
    SELECT c.EntityId, c.Name, c.ParentId
    FROM items AS c
    LEFT JOIN LATERAL (
        SELECT s.QueryId
        FROM queries AS q
        WHERE q.QueryId = c.EntityId
    ) AS t ON TRUE
    WHERE e.unnest = c.ParentId
    ORDER BY t.QueryId DESC
) AS t0 ON TRUE

问题点 #

DefaultIfEmpty将外层SelectMany转为LEFT JOIN，但实际期望仅内层子查询使用LEFT JOIN。尝试使用GroupJoin会破坏现有逻辑，无法采用。

解决方法：调整查询结构 #

将两层SelectMany拆分后问题解决，修改后的代码如下：

DbSet<MyRecord<long>>().FromSql("select * from unnest({0})", new object[] { NpgsqlParameter<MyRecord<long>[]>() })
.SelectMany(pk => dataContext.TestItems
    .Where(item => pk.Item == item.ParentId))
.SelectMany(entity => dataContext.TestQueries.Where(a => a.QueryId == entity.EntityId)
            .DefaultIfEmpty(), 
        (left, right) => new JoinResult<TestItem, TestQuery>{Left = left, 
            Right = right })
    .OrderByDescending(result => result.Right.ObjectId)
    .Select(result => result.Left)

疑问 #

仍认为DefaultIfEmpty的行为不符合预期，它应该仅修改内层子查询的关联类型，而保留外层关联类型不变。

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