Linq .GroupBy() with count

Question

I have a table that I need to summarize in a report. This is my sample table.

                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

I am trying to summarize this to pass back in my viewmodel without a loop. This is the result that I am attempting to achieve.

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

How can I use GroupBy and Count to achieve my desired results? Would that be the best approach to take?

Answer 1

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

Answer 2

if items are fixed:

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