How to use cross apply with Sql Kata

Question

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

Answer 1

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

check this example on 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"); // <---

Answer 2

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.