如何与SqlKata库进行原始交叉连接

Question

我将PostgreSQL与C#与SqlKata库结合使用。如何使用SqlKata库进行这样的查询？

select rt.id as report_template_id, 
       string_agg(rtb.code, ',' order by b.idx) as aggregated_code
from report_template rt
  cross join unnest(rt.template_blocks_id) with ordinality as b(template_id, idx)
  join report_template_block rtb on rtb.id = b.template_id
group by rt.id
order by rt.id;

Answer 1

您可以使用FromRaw来实现这一点。

var query = new Query().FromRaw("report_template rt cross join unnest(rt.template_blocks_id) with ordinality as b(template_id, idx)")
  .Join("report_template_block as rtb", "rtb.id", "b.template_id")
  .GroupBy("rt.id")
  .OrderBy("rt.id");

检查SqlKata游乐场上的示例

非常类似于这个包含SQLKata语句的FromRaw更新查询