Search code examples
sqlsqlkata

How to use cross apply with Sql Kata

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
Solution

  • 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"); // <---