Linq C# Sum in Group By
本文关键字:Group By in Sum Linq | 更新日期: 2023-09-27 18:06:41
我正试图转换这里的SQL http://sqlfiddle.com/#!6/a1c8d/2在下面的linq。预期的结果是sqlfiddle中的结果,但是我的LINQ返回更多的行。
PS:在sqlfiddle中,字段被减少以不增加污染,并专注于我的问题。
resultado.Dados =
(
from a in db.AgendaHorario
join b in db.Agenda on a.AgendaID equals b.AgendaID
select new
{
a.AgendaID,
Horario = a.Horario,
Controle = a.Controle,
Cor = b.Cor,
Agenda = b.Sigla
}).AsEnumerable()
.GroupBy(g => new
{
g.AgendaID,
Horario = g.Horario.ToString("dd/MM/yyyy"),
Data = g.Horario.ToString("yyyy-MM-dd"),
g.Controle,
g.Agenda,
g.Cor
})
.Select(s => new
{
id = s.Key.AgendaID,
title = s.Key.Agenda,
start = s.Key.Data,
color = String.IsNullOrEmpty(s.Key.Cor) ? "3a87ad" : s.Key.Cor,
className = "",
someKey = 1,
allDay = false,
Resultado0 = s.Sum(m => m.Controle == "L" ? 1 : 0).ToString(),
Resultado1 = s.Sum(m => m.Controle == "B" ? 1 : 0).ToString()
});
根据评论,这解决了如何在Linq中重复SqlFiddle的问题。请注意,对String Date的投影不能直接转换为Sql,因此我不得不提前实现AsEnumerable()
(显然,在实际查询中,在实现之前应用任何过滤器!)。您可以使用SqlFunctions对日期部分进行分组,例如,SqlFunctions.DatePart
的3个应用程序将允许您按dd, MM和YYYY进行分组
var dados = db.AgendaHorarios1
.AsEnumerable()
.GroupBy(ah => ah.Horario.ToString("dd/MM/yyyy"))
.Select(g => new {Horario = g.Key,
Livre = g.Count(x => x.Controle == "L"),
Bloq = g.Count(x => x.Controle == "B"),
Aged = g.Count(x => x.Controle == "A")});