dotnetcore / dotnetcore/FreeSql
Select.AsTreeCte在LambdaExpression内与一般行为不一致
- Dominant language
- C#
- Stars
- 4.4k
- Forks
- 910
- PR merge metrics
- No merged PRs in 30d
Description
#### 问题描述及重现代码:
Model
```c#
[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_categorys { get; set; }
}
```
Query
```c#
freeSql.Select().Where(x => freeSql.Select().Where(y => true).AsTreeCte(null, true, " -> ", -1).ToList(y => y.Id).Contains(x.Id)).ToList();
var innerQuery = freeSql.Select().Where(y => true).AsTreeCte(null, true, " -> ", -1);
freeSql.Select().Where(x => innerQuery.ToList(y => y.Id).Contains(x.Id)).ToList();
```
SQL
```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
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.