linq-to-sql: GROUP BY and MIN() in SELECT part
本文关键字:in SELECT part MIN GROUP BY and linq-to-sql | 更新日期: 2023-09-27 18:32:21
在MySql中,我有以下(简化的)查询:
SELECT
ct.ColumnA,
MIN(IF(Constant + mt.ColA >= mt.ColB, Constant + mt.ColA, mt.ColB)) AS ResultCol
FROM
master_table mt
INNER JOIN child_table ct ON (ct.MasterID = mt.ID)
WHERE
ct.ColumnB = 1
...
GROUP BY
ct.ColumnA;
这个查询在MySql中完美运行,但我无法将其转换为Linq2sql。
好的,这是我是如何做到的(没有完全使用 linq2sql 测试它,我使用 linq2db 项目):
var q =
from mt in db.MasterTable
join ct in db.ChildTable on mt.ID equals ct.MasterID
where
ct.ColumnB = 1 &&
...
group new { ct, mt } by new { ct.ColumnA } into g
select
new
{
g.Key.ColumnA,
ResultCol = g.Min(x => myConstant + x.mt.ColA > x.mt.ColB
? myConstant + x.mt.ColA
: x.mt.ColB)
};