group clause (C# Reference)

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()
              };

字符串

如果项目是固定的：

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;
}

字符串

使用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

字符串