I tried writing this code to use SELECT DISTINCT ON
:
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);
q.SelectExpression = q.SelectExpression.Replace("SELECT",
$"SELECT DISTINCT ON ({q.Column<WatchedUrlScanResult>(x => x.WatchedUrlRecordId)})");
var fromSnapshot = Db.Select<WatchedUrlScanResult>(q);
In debugger it shows the value has set for SelectExpression
but the actual SQL that runs is just SELECT
without DISTINCT ON
.
I tried:
q.Select(q.SelectExpression.Replace("SELECT",
$"SELECT DISTINCT ON ({q.Column<WatchedUrlScanResult>(x => x.WatchedUrlRecordId)})"));
But I get:
Potential illegal fragment detected
Is it possible to do this without making full query a string? (using {q.Column<Table>(x => x.ColumnName)}
in a full string query is fine but not very readable compared to fluent syntax)
If you want to perform a SELECT DISTINCT query in OrmLite using its SqlExpression<T>
you would use the SelectDistinct() API, e.g:
var results = Db.Select(q.SelectDistinct());
But as you're trying to perform your own custom SQL distinct Query you'll need to use the Custom SQL APIs, e.g:
var sql = q.ToSelectStatement().Replace(what,with);
var results = Db.SqlList<WatchedUrlScanResult>(sql);