dotnetcore / dotnetcore/FreeSql

字段配置 `RereadSql` 特性后进行多表查询特定情况下异常

Open
#2,261 4 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

#### 问题描述及重现代码:
如果属性使用 `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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.