LINQ with SUM() JOIN
本文关键字:JOIN SUM with LINQ | 更新日期: 2023-09-27 18:33:56
我正在尝试将此SQL句子翻译成LINQ,但我对LINQ很陌生
select professor.nom, professor.llinatge,
SUM(case when falta.aprovada = 1 then 1 else 0 end) as FJ,
SUM(case when falta.aprovada = 0 then 1 else 0 end) as FNJ
from falta inner join professor on falta.id_profe = professor.id_profe
group by professor.llinatge, professor.nom
我无法在带有联接的 LINQ 中完成这项工作。我在 LINQ 中对此最好的建议是:
var query = from f in db.falta
join p in db.professor
on f.id_profe equals p.id_profe
group f by new {p.nom, p.llinatge, f.aprovada} into g
select new
{
nombre = g.Key.nom + " "+ g.Key.llinatge,
fj = g.Select(s=> s.aprovada == true).Count(),
fnj = g.Select(s=> s.aprovada == false).Count()
};
谢谢!
你可以试试 SQL to LINQ ...多年来,我总是发现Linq Pad是一个非常方便的工具......
最后,我找到了与我的 SQL 语句匹配的 LINQ 查询:
from falta in db.Falta
join professor in db.Professor on falta.Id_profe equals professor.Id_profe
group new {professor, falta} by new {
professor.Llinatge,
professor.Nom
} into g
select new {
g.Key.Nom,
g.Key.Llinatge,
FJ = (System.Int64?)g.Sum(p => (
p.falta.Aprovada == true ? 1 : 0)),
FNJ = (System.Int64?)g.Sum(p => (
p.falta.Aprovada == false ? 1 : 0))
};
感谢您的帮助!希望这有帮助!