linq - Linq 中的子查询

Question

有人能告诉我如何将此查询转换为 linq 吗？

SELECT dpr_ts
      ,dpr_close
      ,nvl((SELECT pay.pay_dividend
          FROM pay
         WHERE qot_sec_id = pay_sec_id
               AND pay.pay_date = dpr_ts
               AND rownum = 1),0) dividend
  FROM dpr_mm, qot
 WHERE qot_id = dpr_qot_id
       AND qot_id = 2027314299;

Answer 1

var query
= from dpr in ctx.DPR_MM 
  join qot in ctx.QOT on dpr.DPR_QOT_ID equals qot.QOT_ID
  join pay in ctx.PAY on new { p1 = (decimal) qot.QOT_SEC_ID, p2 = dpr.DPR_TS }
  equals new { p1 =  (decimal)pay.PAY_SEC_ID , p2 = pay.PAY_DATE  }
  into ps
  from pay in ps.DefaultIfEmpty()
  where qot.QOT_ID == 2027314299
  select new
  {
      dpr.DPR_TS,
      dpr.DPR_CLOSE,
      pay_dividend = pay.PAY_DIVIDEND == null ? 0 : pay.PAY_DIVIDEND
  };