带计数的Linq.GroupBy()

Question

我有一个需要在报告中汇总的表。这是我的样品表

                Orders
_____________________________________
CustomerId | CustomerName | OrderType 
___________|______________|__________ 
1          |     Adam     | Shoe
1          |     Adam     | Shoe
1          |     Adam     | Shoe
1          |     Adam     | Hat
1          |     Adam     | Hat
2          |     Bill     | Shoe
2          |     Bill     | Hat
3          |     Carl     | Sock
3          |     Carl     | Hat

我正试图总结这一点，以便在没有循环的情况下返回到我的视图模型中。这就是我正在努力实现的结果。

CustomerName | Shoe | Hat | Sock | Total Orders
------------ | ---- | --- | ---- | ------------
Adam         |   3  |  2  |  0   |      5
Bill         |   1  |  1  |  0   |      2
Carl         |   0  |  1  |  1   |      2
//var resultList = dbContext.Orders.OrderBy(o => o.CustomerId);

如何使用GroupBy和Count来实现我想要的结果？这会是最好的方法吗？

Answer 1

group子句（C#引用）

var summary = from order in dbContext.Orders
              group order by order.CustomerId into g
              select new { 
                  CustomerName = g.First().CustomerName , 
                  Shoe = g.Count(s => s.OrderType == "Shoe"),
                  Hat = g.Count(s => s.OrderType == "Hat"),
                  Sock = g.Count(s => s.OrderType == "Sock"),
                  TotalOrders = g.Count()
              };

Answer 2

如果项目是固定的：

public List<OrderViewModel> GetCustOrders()
{
    var query = orders
        .GroupBy(c => c.CustomerName)
        .Select(o => new OrderViewModel{
            CustomerName = o.Key,
            Shoe = o.Where(c => c.OrderType == "Shoe").Count(c => c.CustomerId),
            Hat = o.Where(c => c.OrderType == "Hat").Count(c => c.CustomerId),
            Sock = o.Where(c => c.OrderType == "Sock").Count(c => c.CustomerId),
            Total = o.Count(c => c.CustomerId)
        });
    return query;
}

Answer 3

使用SQL是一个选项，我测试了它，并得到了您想要的：

select p.*, t.total as 'Total Orders' from 
(
    select CustomerName, count(CustomerId) total from Orders group by CustomerName
) as t inner join
(
    select * from Orders
    pivot(count(CustomerId) 
        for OrderType in ([Shoe], [Hat], [Sock])
        ) as piv
)as p on p.CustomerName = t.CustomerName