How to convert and optimize this SQL query in SqlKata

Question

I have this SQL query and I need to convert it SqlKata

SELECT VVIAGGIO AS VIAGGIO, ISNULL(AEAN,'') AS EAN
FROM 
SALDI V INNER JOIN ARTICOLI A ON VARTI=AARTI
INNER JOIN ORDI OT ON VSTAB=OTSTAB AND VMAGA=OTMAGA AND VAGG=OTRAGG
  WHERE VORDI ='21'
  AND VHOST ='68'
ORDER BY VPROG, AARTI

I don't know how to structure it because here is ISNULL(), INNER JOIN.. I already checked SqlKata select instruction.

Any suggestions on how to optimize and convert this query in SqlKata?

Answer 1

here is what you need:

var query = new Query("SALDI as V")
    .Join("ARTICOLI as A","A.ARTI","V.ARTI")
    .Join("ORDI as OT",j => j.On("OT.STAB","V.STAB")
          .On("V.MAGA","OT.MAGA")
          .On("V.AGG","OT.RAGG")
    )
    .Where("V.ORDI","21")
    .Where("V.HOST","68")
    .Select("V.VIAGGIO")
    .SelectRaw("ISNULL(V.AEAN,'') as EAN")
    .OrderBy("V.PROG", "A.ARTI")