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

[AiYuZhen]

问题描述及重现代码：

如果属性使用 ColumnAttribute 特性且配置了 RereadSql 属性时，在进行多表关联查询并对每个 ISelect 实例设置 'AsType' 时，会报无对应字段的异常。
生成的 SQL 语句如下：异常为：SQL logic error no such column: ag.GEOMETRY

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 语句：

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# code
//实体定义
[Table(Name = "GXB")]
public class ZGXB
{
    /// <summary>
    /// 主键ID（自增）
    /// </summary>
    [Column(IsPrimary = true, IsIdentity = true)]
    public int OGC_ID { get; set; }

    /// <summary>
    /// 对象ID（唯一标识）
    /// </summary>
    [MaxLength(36)]
    public string? OBJECTID { get; set; }

    /// <summary>
    /// 权利人编码（关联权利人表）
    /// </summary>
    [MaxLength(27)]
    public string? QLRBM { get; set; }
}

[Table(Name = "GXB_YSK")]
public class ZGXB_YSK : ZGXB
{
    /// <summary>
    /// 新权利人编码。
    /// </summary>
    [Column(IsIgnore = true)]
    public string? NewQLRBM { get; set; }
}

/// <summary>
/// 农权宗地新数据表。
/// </summary>
[Table(Name = "NQZDATTR")]
public class ZNQZD
{
    /// <summary>
    /// 主键ID（自增）
    /// </summary>
    [Column(IsPrimary = true, IsIdentity = true)]
    public int OGC_ID { get; set; }

    /// <summary>
    /// 对象ID（唯一标识）
    /// </summary>
    [MaxLength(36)]
    public string? OBJECTID { get; set; }
}

/// <summary>
/// 农权宗地原始库表。
/// </summary>
[Table(Name = "NQZDATTR_YSK")]
public class ZNQZD_YSK : ZNQZD 
{ 
    /// <summary>
    /// 新地块编码。
    /// </summary>
    public string? NewDKBM { get; set; }
}

[Table(Name = "GEOPY#NQZDATTR")]
public class ZGEOPY
{
    /// <summary>
    /// 主键ID（自增）
    /// </summary>
    [Column(IsPrimary = true, IsIdentity = true)]
    public int OGC_ID { get; set; }

    /// <summary>
    /// 对象ID（唯一标识，关联业务表）
    /// </summary>
    public string? OBJECTID { get; set; }

    /// <summary>
    /// 几何图形
    /// </summary>
    //[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<ZGXB>().AsType(typeof(ZGXB_YSK));
    var selectDk = freesql.Select<ZNQZD>().AsType(typeof(ZNQZD_YSK));
    var selectGeo = freesql.Select<ZGEOPY>().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

[2881099]

1. Select0Provider.cs 文件搜索 IsDefaultSqlContent：

public bool IsDefaultSqlContent => _tables.Count == 1 && _tables[0].Table?.AsTableImpl == null &&
            _distinct == false && _is_AsTreeCte == false && _where.Length == 0 && _join.Length == 0 &&
            string.IsNullOrWhiteSpace(_orderby) && string.IsNullOrWhiteSpace(_groupby) && string.IsNullOrWhiteSpace(_tosqlAppendContent) &&
            _aliasRule == null && _selectExpression == null && _tables[0].Table.Columns.All(a => string.IsNullOrWhiteSpace(a.Value.Attribute.RereadSql));
// 增加了 && _tables[0].Table.Columns.All(a => string.IsNullOrWhiteSpace(a.Value.Attribute.RereadSql))

2. 测试代码

var select2 = freesql.Select<ZNQZD>().AsType(typeof(ZNQZD_YSK)) as Select0Provider;
var af = select2.GetExpressionField(selector, FieldAliasOptions.AsProperty);
Console.WriteLine(af.field);

---

有空帮测试下这两项，目前用 macOS 不方便

[AiYuZhen]

已修改 Select0Provider.cs 文件中的相关代码

这样测试不知道对不对，测试代码：

using System.Linq.Expressions;

using FreeSql;
using FreeSql.Internal.CommonProvider;

using FreeSqlTest;

try
{
    var freesql = new FreeSqlBuilder()
        .UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
        .UseAutoSyncStructure(true)
        .Build();

    Expression<Func<ZNQZD, string?>> funZd = zd => zd.OBJECTID; 
    Expression<Func<ZGEOPY, string?>> funGeo = geo => geo.GEOMETRY;

    var select2 = freesql.Select<ZNQZD>().AsType(typeof(ZNQZD_YSK)) as Select0Provider;
    var af = select2!.GetExpressionField(funZd, FieldAliasOptions.AsProperty);
    Console.WriteLine("\n第一个结果：");
    Console.WriteLine(af.field);

    var select3 = freesql.Select<ZGEOPY>().AsType(typeof(ZGEOPY_YSK)) as Select0Provider;
    var af2 = select3!.GetExpressionField(funGeo, FieldAliasOptions.AsProperty);
    Console.WriteLine("\n第二个结果：");
    Console.WriteLine(af2.field);
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    Console.WriteLine("按任意键关闭");
    _ = Console.ReadKey();
}

输出结果：

第一个结果：
zd."OBJECTID"

第二个结果：
substring(geo."GEOMETRY",1,2)