How to use cross apply with Sql Kata-CodePudding

I am dont know to construct a query with Cross Apply and SqlKata. I was searching the net and I find out that cross apply was not supported by SqlKata. Is there any other way to achieve my requirements.

var query = new Query("Test as t")

cross apply
(select top 1 t2.TestID from Test as t2  where t1.LegID = t2.LegID order by t2.Sequence desc)

This is the sql query

select * from Test1 t1
cross apply
(select top 1 s.OperationalStopID
    from Test2 t2
    where t2.LegID = t1.LegID
    order by t2.SequenceNumber desc) t3

CodePudding user response：

I am not familiar with sqlkata, but you can rewrite your SQL query to an inner join, like:

select DISTINCT
  t1.I,
  t1.LegID,
  x.OperationalStopID
from test1 t1
inner join (select LegID, MAX(OperationalStopID) as OperationalStopID
            from test2
            group by LegID) x on x.LegID = t1.LegID

I hope you are able to convert this SQL query to sqlkata syntax ?

This query is tested here: DBFIDDLE

P.S.: oops, the DISTINCT should not have been in there anymore, please remove it.

CodePudding user response：

Just use the Join method and pass "CROSS APPLY" or whatever method you like in the last parameter

check this example on j, "CROSS APPLY");" rel="nofollow noreferrer">SqlKata Playground

var another = new Query("Test2 as t2")
    .WhereColumns("t2.LegID", "=", "t1.LegID")
    .Select("s.OperationalStopID")
    .OrderByDesc("t2.SequenceNumber")
    .Limit(1);
    

var query = new Query("Test1 as t1").Join(another.As("t3"), j => j, "CROSS APPLY"); // <---