gpt4 book ai didi

c# - 如何将左外连接添加到分组和求和的 LINQ 查询中

转载 作者:行者123 更新时间:2023-11-30 14:34:36 25 4
gpt4 key购买 nike

我有一张员工表:

EmployeeID  |  EmployeeName
---------------------------
1 | Jack
2 | Jill
3 | Roger

还有一个出现表:

OccurrenceID  |  EmployeeID  |  Points
--------------------------------------
1 | 1 | 5
2 | 2 | 3
3 | 1 | 1

我有一个有效的 LINQ 查询,可以将两个表组合在一起并求和:

groupedOccurrences = (from o in db.Occurrences.Include(o => o.Employee)
where o.OccurrenceDate >= beginDate
&& o.OccurrenceDate <= endDate
group o by o.Employee.EmployeeName into g
select new OccurrenceByQuarter
{
Name = g.Key,
Total = g.Sum(o => o.Points)
});

产生这个输出:

 Jack 6
Jill 3

但我还希望员工 Roger 以 0 分出现在输出中。我试过像这样向 LINQ 查询添加连接:

groupedOccurrences = (from e in db.Employees
from o in db.Occurrences
join o in db.Occurrences on e.EmployeeID equals o.EmployeeID into j1
from j2 in j1.DefaultIfEmpty()
group j2 by e.EmployeeName into g
select new OccurrenceByQuarter
{
Name = g.Key,
Total = g.Sum(o => o.Points)
});

但我最终得到的点数被大大夸大了(就像它们应该是的 24 倍)。

我还尝试通过将 Total 的声明更改为 public int,让 Total 在 null 时返回 0?总计{得到;放; } 在我的 OccurrencesByQuarter 类中,但是当我尝试更改 LINQ 查询以包含 Total = g.Sum(o => o.Points) ?? 0 我收到一条错误消息“运算符 ?? 不能应用于 int 和 int 类型的操作数”。

如有任何帮助,我们将不胜感激。

最佳答案

使用群组加入:

groupedOccurrences = (from e in db.Employees
join o in db.Occurrences.Where(x =>
x.OccurrenceDate >= beginDate &&
x.OccurrenceDate <= endDate)
on e.EmployeeID equals o.EmployeeID into g
select new OccurrenceByQuarter
{
Name = e.EmployeeName,
Total = g.Sum(x => (int?)x.Points) ?? 0
});

结果将是:

Jack  6
Jill 3
Roger 0

为了为空组返回0,将汇总属性转换为可为空,然后应用空合并运算符返回默认值:g.Sum(x => (int?) x.Points) ?? 0

关于c# - 如何将左外连接添加到分组和求和的 LINQ 查询中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13767767/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com