FreeSql中With...As(...)的一种实现

Sql:

WITH tmp AS ( SELECT cardnum, MAX ( receivetime ) AS receivetime FROM uwb20230626 GROUP BY cardnum ) 
SELECT b.*, A.farfromroadway, A.backroadwaydirection 
FROM
worksite	A 
INNER JOIN uwb20230626 b ON A.NO = b.sitenum
INNER JOIN tmp ON b.receivetime = tmp.receivetime AND b.cardnum = tmp.cardnum

FreeSql

用ToSql模拟With...AS(...)

var sql = db.Fsql.Select<UwbLoc>()
    .AsTable((t, old) => t == typeof(UwbLoc) ? tablename : old)
    .FromQuery(db.Fsql.Select<UwbLoc>()
         .AsTable((t, old) => tablename)
         .GroupBy(a => a.cardnum)
         .WithTempQuery(g => new
         {
              cardnum = g.Key,
              receivetime = g.Max(g.Value.receivetime)
         })
     )
    .Where((a, b) => a.cardnum == b.cardnum && a.receivetime == b.receivetime)
    .WithTempQuery((a,b) => a)  //此处必须要wisthtempQuery，不能直接在tosql((a,b) => a)，因为tosql会将列结果加 as as1，使下面withsql列名不一致
    .ToSql();

var data = await db.Fsql.Select<UwbLoc, WorkSite>()
    .WithSql(sql, null)         //sql里已经有astable处理，这不用再添加astable，否则会重复，变成union select联合查询
    .InnerJoin((x, y) => x.sitenum == y.No && y.isdel == false)
    .Where((x, y) => x.distance > y.FarFromRoadWay && y.BackRoadWayDirection != 0 && x.direction == 2 / y.BackRoadWayDirection)
    .ToListAsync((x, y) => new WorkSiteCfgDto
    {
        Distance = x.distance,
        No = y.No,
        Name = y.Name,
        FarFromRoadWay = y.FarFromRoadWay,
    });

注意

• AsTable会通过WithSql传入查询中，所以不需要再次AsTable，否则多次AsTable会变成Union Select
• ToSql(x => {...}) 会在sql中自动添加列命名cardnum as as1，导致列名不一致报列不存在错误，须WithTempQuery生成子查询结果