dotnetcore / dotnetcore/FreeSql
字段配置 `RereadSql` 特性后进行多表查询特定情况下异常
- Dominant language
- C#
- Stars
- 4.4k
- Forks
- 910
- PR merge metrics
- No merged PRs in 30d
Description
#### 问题描述及重现代码:
如果属性使用 `ColumnAttribute` 特性且配置了 `RereadSql` 属性时,在进行多表关联查询并对每个 `ISelect` 实例设置 'AsType' 时,会报无对应字段的异常。
生成的 SQL 语句如下:异常为:`SQL logic error no such column: ag.GEOMETRY`
```sql
SELECT a."OGC_ID" as1, a."OBJECTID" as2, a."QLRBM" as3, an."OGC_ID" as4, an."OBJECTID" as5, ag."OGC_ID" as6, ag."OBJECTID" as7, substring(ag."GEOMETRY",1,2) as8
FROM "GXB_YSK" a
LEFT JOIN (
SELECT a."OGC_ID", a."OBJECTID", a."NewDKBM"
FROM "NQZDATTR_YSK" a) an ON a."OBJECTID" = an."OBJECTID"
LEFT JOIN (
SELECT a."OGC_ID", a."OBJECTID", substring(a."GEOMETRY",1,2)
FROM "GEOPY#NQZDATTR_YSK" a) ag ON an."OBJECTID" = ag."OBJECTID"
WHERE (a."QLRBM" = '')
```
期望的 SQL 语句:
```sql
SELECT a."OGC_ID" as1, a."OBJECTID" as2, a."QLRBM" as3, an."OGC_ID" as4, an."OBJECTID" as5, ag."OGC_ID" as6, ag."OBJECTID" as7, substring(ag."GEOMETRY",1,2) as8
FROM "GXB_YSK" a
LEFT JOIN (
SELECT a."OGC_ID", a."OBJECTID", a."NewDKBM"
FROM "NQZDATTR_YSK" a) an ON a."OBJECTID" = an."OBJECTID"
LEFT JOIN (
SELECT a."OGC_ID", a."OBJECTID", substring(a."GEOMETRY",1,2) as "GEOMETRY"
FROM "GEOPY#NQZDATTR_YSK" a) ag ON an."OBJECTID" = ag."OBJECTID"
WHERE (a."QLRBM" = '')
```
```c#
// c# code
//实体定义
[Table(Name = "GXB")]
public class ZGXB
{
///
/// 主键ID(自增)
///
[Column(IsPrimary = true, IsIdentity = true)]
public int OGC_ID { get; set; }
///
/// 对象ID(唯一标识)
///
[MaxLength(36)]
public string? OBJECTID { get; set; }
///
/// 权利人编码(关联权利人表)
///
[MaxLength(27)]
public string? QLRBM { get; set; }
}
[Table(Name = "GXB_YSK")]
public class ZGXB_YSK : ZGXB
{
///
/// 新权利人编码。
///
[Column(IsIgnore = true)]
public string? NewQLRBM { get; set; }
}
///
/// 农权宗地新数据表。
///
[Table(Name = "NQZDATTR")]
public class ZNQZD
{
///
/// 主键ID(自增)
///
[Column(IsPrimary = true, IsIdentity = true)]
public int OGC_ID { get; set; }
///
/// 对象ID(唯一标识)
///
[MaxLength(36)]
public string? OBJECTID { get; set; }
}
///
/// 农权宗地原始库表。
///
[Table(Name = "NQZDATTR_YSK")]
public class ZNQZD_YSK : ZNQZD
{
///
/// 新地块编码。
///
public string? NewDKBM { get; set; }
}
[Table(Name = "GEOPY#NQZDATTR")]
public class ZGEOPY
{
///
/// 主键ID(自增)
///
[Column(IsPrimary = true, IsIdentity = true)]
public int OGC_ID { get; set; }
///
/// 对象ID(唯一标识,关联业务表)
///
public string? OBJECTID { get; set; }
///
/// 几何图形
///
//[Column(RereadSql = "st_AsText111({0})", CanUpdate = false)]
[Column(RereadSql = "substring({0},1,2)", CanUpdate = false)]
public string? GEOMETRY { get; set; }
}
[Table(Name = "GEOPY#NQZDATTR_YSK")]
public class ZGEOPY_YSK : ZGEOPY { }
//测试代码
using FreeSql;
using FreeSqlTest;
try
{
var freesql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build();
var selectGxb = freesql.Select().AsType(typeof(ZGXB_YSK));
var selectDk = freesql.Select().AsType(typeof(ZNQZD_YSK));
var selectGeo = freesql.Select().AsType(typeof(ZGEOPY_YSK));
var selectDks = selectGxb.FromQuery(selectDk, selectGeo)
.LeftJoin((ax, an, ag) => ax.OBJECTID == an.OBJECTID)
.LeftJoin((ax, an, ag) => an.OBJECTID == ag.OBJECTID)
.Where((ax, an, ag) => ax.QLRBM == "");
var sql = selectDks.ToSql((ax, an, ag) => new { ax, an, ag });
Console.WriteLine(sql);
var result = selectDks.ToList((ax, an, ag) => new { ax, an, ag });
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.Read();
}
```
#### 数据库版本
`Sqlite`
#### 安装的Nuget包
`FreeSql 3.5.310`
`FreeSql.Provider.Sqlite 3.5.310`
#### .net framework/. net core? 及具体版本
`.NET10.0`
[FreeSqlTest.zip](https://github.com/user-attachments/files/30287972/FreeSqlTest.zip)
Contributor guide
No contributing guide indexed for this repository
Research direction
Start with the attached FreeSqlTest reproduction using SQLite, focusing on Select().AsType(), FromQuery(), and Column(RereadSql). Compare ToSql() with the expected SQL, then run ToList() to reproduce the missing-column error. Done means the generated subquery exposes the transformed GEOMETRY expression with its expected alias and the reproduction succeeds.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sqlite
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 68/100