dotnetcore / dotnetcore/FreeSql

Select.AsTreeCte在LambdaExpression内与一般行为不一致

Open
#2,094 2 comments 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.