c# - ServiceStack、LeftJoin 查询

Question

我有这个 SQL 代码，我想把它转换成 ormlite - 但我不知道如何以最好的方式做到这一点。

SELECT  *
FROM Job 
INNER JOIN Emp ON Job.JobAnsvarID = Emp.EmpId
LEFT JOIN (SELECT JobId, MIN(TimeReg.RegDate) AS TimeMinDate FROM TimeReg WHERE RegHrs IS NOT NULL AND JournNo = 0 GROUP BY JobId) AS t ON t.JobId = Job.JobID
WHERE   NOT (t.TimeMinDate IS NULL)

我知道我可以使用 CustomJoin 和 UnsafeWhere，但如果可能的话我想避免使用硬编码文本。

现在我有这个，但我想再次避免硬编码文本。

var ev = Db.From<Job>();
ev.CustomJoin("LEFT JOIN (SELECT {TimeReg.JobId}, MIN({TimeReg.RegDate}) AS MinDate FROM {TimeReg} WHERE {TimeReg.RegHrs} IS NOT NULL AND {TimeReg.JournNo} = 0 GROUP BY {TimeReg.JobId}) AS t ON t.JobId = {Job.JobID}"
            .ReplaceAll("{Job.JobID}", GetQuotedColumnName<Job>(x => x.Id, true))
            .ReplaceAll("{TimeReg.JobId}", GetQuotedColumnName<TimeRegDTO>(x=>x.JobId, true))
            .ReplaceAll("{TimeReg.RegDate}", GetQuotedColumnName<TimeRegDTO>(x => x.RegistrationDate, true))
            .ReplaceAll("{TimeReg.RegHrs}", GetQuotedColumnName<TimeRegDTO>(x => x.Hours, true))
            .ReplaceAll("{TimeReg.JournNo}", GetQuotedColumnName<TimeRegDTO>(x => x.JournalNumber, true))
            .ReplaceAll("{TimeReg}", GetQuotedTableName<TimeRegDTO>()));

GetQuotedColumnName 只需从 DTO 中获取别名并使用它

Answer 1

没有 OrmLite 没有用于子查询自定义连接的类型化 API 只是 IN SubSelect queries .

我不会替换 HTML，而是使用 C# 字符串插值，还有一种更简单的类型化方法可以使用 nameof ，例如:

var q = Db.From<Job>();
q.CustomJoin($"LEFT JOIN (SELECT {nameof(Job.Id)} ...")

如果您的属性没有别名或者您正在使用自定义命名约定，您将能够使用它。

否则为了使这个用例更好一点，我添加了新的 .Column<Table>()和 .Table<T>() this commit 中的扩展方法这将允许您在自定义 SQL 中使用类型化 API，例如:

q.CustomJoin($"LEFT JOIN (SELECT {q.Column<Job>(x => x.Id)} ...")
q.CustomJoin($"LEFT JOIN (SELECT {q.Column<Job>(nameof(Job.Id))} ...")

q.CustomJoin($"LEFT JOIN (SELECT {q.Column<Job>(x => x.Id, tablePrefix:true)} ...")
//Equivalent to:
q.CustomJoin($"LEFT JOIN (SELECT {q.Table<Job>()}.{q.Column<Job>(x => x.Id)} ...")

此更改从现在的 v5.0.3 开始可用 available on MyGet .