c# - LINQ to SQL - Count number of certain item types in each order -

for sake of simplicity of post, suppose have data in orders table follows. here customerid foreign key customers table. question: how can write linq query find count of vegetables (v) , fruits (f) each customer ordered?

orders table:

orderid | customerid | ordertype 1       | 11         | v 2       | 11         | v 3       | 11         | f 4       | 11         | v 5       | 12         | v 6       | 15         | f 7       | 15         | v 8       | 15         | f 

i can count number of orders each customer follows. but how number of vegetables , number of fruits in each order?:

var query1 = o in orders                 group o o.customerid                 grp                 select new {customerid = grp.key, ordercount = grp.count()}; 

you can use subquery:

var query1 = o in orders              group o o.customerid grp              select new {                  customerid = grp.key,                  ordercount = grp.count(),                  ordercounts = g in grp                                group g g.ordertype grp2                                select new { ordertype = grp2.key, count = grp2.count() }              }; 

or can group both customerid , ordertype:

var query1 = o in orders              group o new { o.customerid, o.ordertype } grp              select new {                  customerid = grp.key.customerid,                  ordertype = grp.key.ordertype,                  ordercount = grp.count()              }; 

they both return same data in different form.