Michael Michael - 5 days ago 5
C# Question

csharp group by issue

Hi guys i have a question about Linq.

I group the result on the customers zipcode. For each zipcode i want to see the amount of bookings and the amount of equipment that is ordered.

So far my code looks like this:

var statistics = from b in db.Bookings
from c in db.Customers
where b.customerID == c.id
group c by c.zipcode into stat
select new {
Zipcode = stat.Key,
NumberOfBookings = stat.Count()
};


This code groups result into zipcodes and gives me the amount of bookings in each zipcode. How to i get the amount of equipment also?

Answer

Rather than using joins like in SQL, you can (and it's better) use the navigation properties from your model:

var statistics = 
    from b in db.Bookings
    group b by b.Customer.zipcode into g
    select new
    { 
        Zipcode = g.Key,
        NumberOfBookings = g.Count(),
        NumberOfEquipments = g.SelectMany(b => b.Equipments).Count(),
    };

Note that the g variable represents a set of bookings with the same zipcode, so SelectMany is used to get all associated equipments before applying the Count operator.

Of course that's not the only way, for instance you can use Sum instead:

NumberOfEquipments = g.Sum(b => b.Equipments.Count())
Comments