ServiceStack, LeftJoin query

I have this SQL code, and I want this converted to ormlite - but I don't know how to do this the best way.

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)

I know I could use CustomJoin and UnsafeWhere, but if possible I want to avoid using hardcoded text.

Right now I have this, but again, I want to avoid hardcoded text.

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 just take the Alias name from the DTO and use this

No OrmLite doesn't have a Typed API for Custom Joins on Sub Queries just IN SubSelect queries.

Instead of replacing HTML I would just use C# string interpolation, also a simpler Typed approach could be to use nameof, e.g:

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

Which you'll be able to use if your properties aren't aliased or you're using a custom naming convention.

Otherwise to make the use-case for this a little nicer I've added new .Column<Table>() and .Table<T>() extension methods in this commit which will let you use Typed APIs in your custom SQL, e.g:

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)} ...")

This change is available from v5.0.3 that's now available on MyGet.