在linq中将多个列求和并分组为一个值

Question

我有一个包含字段的表:

ID,sales1, sales2, sales3
1   25      15       15
1   10      7        3
2   8       11       9
3   10      5        7
3   22      9        4

我需要按id筛选表并返回sales1 + sales2 + sales3的总和。在mysql中，这将通过

完成

SELECT (SUM(sales1) + SUM(sales2) + SUM(sales3)) AS total
WHERE id = 1
GROUP BY id

但是我不能让它在linq中工作。有人能帮我一下吗?

Answer 1

在LinqPad中划在一起:

void Main()
{
    //since I don't have a suitable database at hand
    //I'm emulating with a list
    var sales = new List<Sale>()
    {
        new Sale() {Id = 1, Sales1 = 25, Sales2= 15, Sales3= 15},
        new Sale() {Id = 1, Sales1 = 10, Sales2= 7, Sales3= 3},
        new Sale() {Id = 2, Sales1 = 8, Sales2= 11, Sales3= 9},
        new Sale() {Id = 3, Sales1 = 10, Sales2= 5, Sales3= 7},
        new Sale() {Id = 3, Sales1 = 22, Sales2= 9, Sales3= 4},
    };
    var filteredSales = sales.GroupBy (s => s.Id)//Need to Group to be able to use .Sum() 
                             //.Where (s => s.Key == 1)
                             .Select (s => new
                              {
                               Id = s.Key,
                               Sum = s.Sum (x => x.Sales1 + x.Sales2 + x.Sales3) 
                              }).Dump(); //ignore Dump. Only available in LinqPad
}
class Sale
{
    public int Id { get; set; }
    public int Sales1 { get; set; }
    public int Sales2 { get; set; }
    public int Sales3 { get; set; }
}

结果:

Id | Sum
1    75
2    28
3    57

取消where子句的注释以按id进行过滤。亲切的问候

Answer 2

var result =
table.GroupBy(x=>x.ID)
.Where(x=>x.Key==1)
.Select(x=>new {total=x.Sum(s=>s.sales1)+x.Sum(s=>s.sales2)+x.Sum(s=>s.sales3)})