CODEWITHSUNDEEP
c#asp.net-mvclinq
pacopicorico
pacopicorico

Reputation: 131

Linq .GroupBy() with count of subtypes and Total Group Count

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?

Upvotes: 6

Views: 19461

Answers (3)

Locke Duan
Locke Duan

Reputation: 55

use SQL is one option, i tested it and get exactly what you want:

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

Upvotes: -3

dotnetspark
dotnetspark

Reputation: 581

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

Upvotes: 9

Nkosi
Nkosi

Reputation: 247008

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

Upvotes: 15

Related Questions