Select.AsTreeCte在LambdaExpression内与一般行为不一致 #2094
Description
问题描述及重现代码：
Model
[Table(Name = "t_category", DisableSyncStructure = true)]
public partial class T_category {
[Column(Name = "id", StringLength = 50, IsPrimary = true, IsNullable = false)]
public string Id { get; set; }
[Column(Name = "parentid", StringLength = 50)]
public string Parentid { get => _Parentid; set {
if (_Parentid == value) return;
_Parentid = value;
Parent_T_category = null;
} }
private string _Parentid;
[Column(Name = "islast")]
public int? Islast { get; set; }
[Navigate("Parentid"), JsonIgnore]
public virtual T_category Parent_T_category { get; set; }
[Navigate("Parentid")]
public virtual List<T_category> T_categorys { get; set; }
}Query
freeSql.Select<T_category>().Where(x => freeSql.Select<T_category>().Where(y => true).AsTreeCte(null, true, " -> ", -1).ToList(y => y.Id).Contains(x.Id)).ToList();
var innerQuery = freeSql.Select<T_category>().Where(y => true).AsTreeCte(null, true, " -> ", -1);
freeSql.Select<T_category>().Where(x => innerQuery.ToList(y => y.Id).Contains(x.Id)).ToList();SQL
/*
实际SQL
*/
SELECT a.*
FROM "t_category" a
WHERE (((a."id") in (SELECT y."id"
FROM "t_category" y
WHERE (1=1))));
/*
预期SQL
*/
SELECT a.*
FROM "t_category" a
WHERE (((a."id") in (WITH RECURSIVE "as_tree_cte"
as
(
SELECT 0 as cte_level, a.*
FROM "t_category" a
WHERE (1=1)
union all
SELECT wct1.cte_level + 1 as cte_level, wct2.*
FROM "as_tree_cte" wct1
INNER JOIN "t_category" wct2 ON wct2."id" = wct1."parentid"
)
SELECT a."id"
FROM "as_tree_cte" a
ORDER BY a.cte_level desc)));--期望可以在LambdaExpression中直接使用AsCte 而不是需要声明单独的ISelect
数据库版本
Postgre 14.13
安装的Nuget包
FreeSql 3.5.212
FreeSql.DbContext 3.5.212
FreeSql.Provider.PostgreSQL 3.5.212
.net framework/. net core？ 及具体版本
.net 8