如何在OrmLite中正确实现SELECT DISTINCT ON查询

问题原因 #

1. 直接修改SelectExpression属性无效，是因为OrmLite执行Select()方法时会根据查询构造器的配置重新生成SELECT语句，覆盖你手动修改的属性值，所以调试时能看到赋值成功，但实际执行的是重写后的语句。
2. 第二种写法触发Potential illegal fragment detected报错，是因为OrmLite的Select()方法对传入的字符串片段有SQL注入防护检测，直接传入拼接的SELECT片段会被判定为风险操作。

可行解决方案 #

方案1：使用UnsafeSelect方法（改动最小）

OrmLite提供了UnsafeSelect扩展方法，专门用于需要传入自定义SQL片段、且你可以确定片段无注入风险的场景，会跳过非法片段检测，代码示例如下：

var q = Db.From<WatchedUrlScanResult>();
q.Join<WatchedUrlRecord>()
    .Where<WatchedUrlRecord>(x => x.TargetDomainRecordId == request.TargetDomainId)
    .And(x => x.ScanDate < fromEnd)
    .OrderBy(x => x.WatchedUrlRecordId)
    .OrderByDescending(x => x.ScanDate);

// 用UnsafeSelect跳过检测，插入DISTINCT ON片段
var distinctOnColumn = q.Column<WatchedUrlScanResult>(x => x.WatchedUrlRecordId);
q.UnsafeSelect(q.SelectExpression.Replace("SELECT", $"SELECT DISTINCT ON ({distinctOnColumn})"));

var fromSnapshot = Db.Select<WatchedUrlScanResult>(q);

这个方案和你原本的实现逻辑完全一致，代码改动最小，执行效率和原生写DISTINCT ON的SQL完全相同。

方案2：用窗口函数实现等价逻辑（完全使用流式语法）

如果你不想使用任何自定义SQL片段，可以用ROW_NUMBER()窗口函数实现和DISTINCT ON完全等价的取分组最新数据的逻辑，全程使用OrmLite原生流式API，不会触发任何检测：

// 内层查询：给每个WatchedUrlRecordId的扫描记录按时间倒序编号
var innerQ = Db.From<WatchedUrlScanResult>();
innerQ.Join<WatchedUrlRecord>()
    .Where<WatchedUrlRecord>(x => x.TargetDomainRecordId == request.TargetDomainId)
    .And(x => x.ScanDate < fromEnd)
    .Select<WatchedUrlScanResult>((res) => new {
        res,
        RowNum = Sql.RowNumber()
            .Over(partitionBy: res.WatchedUrlRecordId, orderBy: innerQ.OrderByDescending(x => x.ScanDate))
    });

// 外层查询：只取每个分组编号为1的最新记录
var outerQ = Db.From(innerQ)
    .Where("RowNum = 1")
    .Select<WatchedUrlScanResult>();

var fromSnapshot = Db.Select<WatchedUrlScanResult>(outerQ);

这个方案完全符合OrmLite的使用规范，没有自定义SQL片段的风险，除了PostgreSQL外也兼容其他支持窗口函数的数据库。

内容的提问来源于stack exchange，提问作者Guerrilla