从数据表中选择数据

Question

我想从数据表中选择数据。目前我正在使用匿名列表。 数据表如下所示：

ID  AMOUNT INVOICE_DATE

1 9,5 06.03.20202 12,4 06.03.20201 12,5 07.03.2020

2 14,3 07.03.2020
3 15 07.03.2020
2 17 08.03.2020
3 19,2 08.03.2020
3 12 08.03.2020

我必须查询的方式是这样的：

ID  AMOUNT  TOTAL_AMOUNT_SOLD INVOICE_DATE

1 12,5 23 07.03.20202 14,3 26,7 07.03.20203 15 15 07.03.2020
2 17 43,7 08.03.2020

3 31,2 46.2 08.03.2020

这是我现在的代码：

var soldInDT = dt.AsEnumerable()
.GroupBy(x => x.Field<int>("ID"))
.Select(grp => new
{
ID = grp.FirstOrDefault().Field<int>("ID"),
AMOUNT = grp.Where(x => x.Field<int>("ID") == grp.Key).Sum(x => x.Field<double>("AMOUNT")),
TOTAL_AMOUNT_SOLD = grp.Where(x => x.Field<int>("ID") == grp.Key && x.Field<DateTime>("INVOICE_DATE") < startDate).Sum(x => x.Field<double>("AMOUNT")),
DATE = grp.FirstOrDefault().Field<DateTime>("INVOICE_DATE")
}).ToList();

有没有比使用 lambda 表达式和这些列表更快的方法呢？

Answer 1

而不是

ID = grp.FirstOrDefault().Field<int>("ID")

只是说

ID = grp.Key

而不是

grp.Where(x => x.Field<int>("ID") == grp.Key).Sum(x => x.Field<double>("AMOUNT"))

只是说

grp.Sum(x => x.Field<double>("AMOUNT"))

而不是

grp.Where(x => x.Field<int>("ID") == grp.Key && x.Field<DateTime>("INVOICE_DATE") < startDate).Sum(x => x.Field<double>("AMOUNT"))

只是说

grp.Where(x => x.Field<DateTime>("INVOICE_DATE") < startDate).Sum(x => x.Field<double>("AMOUNT"))