How to execute OUTER APPLY

ServiceStack.OrmLite
1

Hi guys,
how can i create an OUTER APPLY using ormlite?

I’m trying to execute a query like this

SELECT LastOrder.OrderId
FROM mch.Machines m
OUTER APPLY
(
SELECT TOP 1 orderid, domodified
FROM [prg].[programsmonitor] pm
WHERE pm.machinecode = m.code
ORDER BY domodified DESC
) AS LastOrder
WHERE IsEnable = 1 AND LastOrder.OrderId IS NOT NULL

Tips?
Best regars
Enrico

2

You would need to use Custom SQL to execute non-standard SQL. But you could probably type most of the inner query, something like:

var q = db.From<ProgramsMonitor>()
    .Where("machinecode = m.code")
    .OrderByDescending(x => x.DoModified)
    .Take(1)
    .Select(x => new { x.OrderId, x.DoModified });

var sql = "SELECT LastOrder.OrderId FROM mch.Machines m OUTER APPLY ("
    + q.ToSelectStatement()
    + ") AS LastOrder WHERE IsEnable = 1 AND LastOrder.OrderId IS NOT NULL";

var orderIds = db.SqlColumn<int>(sql);
1 Like
3

Hi,
it is exactly what i did. Are you planning to add this functionality? It might be very helpfull

4

No, you’d need to use Custom SQL for any niche RDBMS-specific queries like this.

5

Ok, thanks for the support
Regards