LINQ按多列分组(Group By)并计算总和(Sum)

LINQ按多列分组(Group By)并计算总和(Sum)

SQL语句：

SELECT * FROM <TableName> GROUP BY <Column1>,<Column2>

QuantityBreakdown
(
    MaterialID int,
    ProductID int,
    Quantity float
)

INSERT INTO @QuantityBreakdown (MaterialID, ProductID, Quantity)
SELECT MaterialID, ProductID, SUM(Quantity)
FROM @Transactions
GROUP BY MaterialID, ProductID

在C#中，如何使用LINQ实现以上SQL语句中的需求呢？方式归纳如下：

方式一

var query = (from t in Transactions
             group t by new {t.MaterialID, t.ProductID}
             into grp
                    select new
                    {
                        grp.Key.MaterialID,
                        grp.Key.ProductID,
                        Quantity = grp.Sum(t => t.Quantity)
                    }).ToList();

方式二

from grouping in list.GroupBy(x => new Tuple<string,string,string>(x.Person.LastName,x.Person.FirstName,x.Person.MiddleName))
select new SummaryItem
{
    LastName = grouping.Key.Item1,
    FirstName = grouping.Key.Item2,
    MiddleName = grouping.Key.Item3,
    DayCount = grouping.Count(), 
    AmountBilled = grouping.Sum(x => x.Rate),
}

方式三

var Results= query.GroupBy(x => new { x.Column1, x.Column2 });

易错提醒：

grp.Key.ProductID, //前往不要忘了Key！！！
Quantity = grp.Sum(t => t.Quantity) //必须取别名Quantity ！！！

---

C# list集合对多个字段group by 、并新增字段

void Main()
{
     var  empList =new List<Employee>
     {
        new Employee {ID = 1, FName = "John", Age = 23, Sex = 'M'},
        new Employee {ID = 2, FName = "Mary", Age = 25, Sex = 'F'},
        new Employee {ID = 3, FName = "Amber", Age = 23, Sex = 'M'},
        new Employee {ID = 4, FName = "Kathy", Age = 25, Sex = 'F'},
        new Employee {ID = 5, FName = "Lena", Age = 27, Sex = 'F'},
        new Employee {ID = 6, FName = "Bill", Age = 28, Sex = 'M'},
        new Employee {ID = 7, FName = "Celina", Age = 27, Sex = 'F'},
        new Employee {ID = 8, FName = "John", Age = 28, Sex = 'M'}
     };
         
    // query with lamda expression g.Key代表Age和Sex两字段，Count为新增字段<br>        // 最终返回的集合QueryWithLamda包含字段：Age、Sex、Count
    var QueryWithLamda = empList.GroupBy(x => new { x.Age,  x.Sex})
                .Select(g=>new {g.Key, Count=g.Count()});
     
    //query with standard expression<br>        //返回结果同上
    var query=from el in empList
              group el by new {el.Age,el.Sex} into g
              select new {g.Key, Count=g.Count()};
  
    foreach (var employee in query /* Or  QueryWithLamda */ )
             Console.WriteLine(employee.Count);
      
}
 
public class Employee
{
  public int ID {get;set;}
  public string FName {get;set;}
  public int Age {get;set;}
  public char Sex {get;set;}
}

---

Linq 中按照多个值进行分组(GroupBy)

/// <summary>要查询的对象</summary>
class Employee {
   public int ID { get;set; }
   public string FName { get; set; }
   public int Age { get; set; }
   public char Sex { get; set; }
}

如果对这个类的Age和Sex的连个字段进行分组，方法如下：

// 先造一些数据
List<Employee> empList = new List<Employee>();
empList.Add(new Employee() {
   ID = 1, FName = "John", Age = 23, Sex = 'M'
});
empList.Add(new Employee() {
   ID = 2, FName = "Mary", Age = 25, Sex = 'F'
});

empList.Add(new Employee() {
   ID = 3, FName = "Amber", Age = 23, Sex = 'M'
});
empList.Add(new Employee() {
   ID = 4, FName = "Kathy", Age = 25, Sex = 'M'
});
empList.Add(new Employee() {
   ID = 5, FName = "Lena", Age = 27, Sex = 'F'
});

empList.Add(new Employee() {
   ID = 6, FName = "Bill", Age = 28, Sex = 'M'
});

empList.Add(new Employee() {
   ID = 7, FName = "Celina", Age = 27, Sex = 'F'
});
empList.Add(new Employee() {
   ID = 8, FName = "John", Age = 28, Sex = 'M'
});

接下来的做法是：

// 实现多key分组的扩展函数版本
var sums = empList
         .GroupBy(x => new { x.Age, x.Sex })
         .Select(group => new {
            Peo = group.Key, Count = group.Count()
         });

foreach (var employee in sums) {
   Console.WriteLine(employee.Count + ": " + employee.Peo);
}

// 实现多key分组的lambda版本
var sums2 = from emp in empList
            group emp by new { emp.Age, emp.Sex } into g
            select new { Peo = g.Key, Count = g.Count() };

foreach (var employee in sums) {
   Console.WriteLine(employee.Count + ": " + employee.Peo);
}

这个例子中就充分利用了匿名类型。