Michael Michael - 1 year ago 101
SQL Question

Linq: Join two tables, group by one column and count another

Hi guys i have a question about Linq. Its a little system representing a booking system for some equipment.

So i have these tables in my linqmodel:

So i want to draw some statistics from my database, where i group the result on the customers zipcode. For each zipcode i want to se the amount of bookings and the amount of equipment that is ordered.

I have made the sql and it looks like this:

select c.zipcode, count(distinct B.id), count(BE.equipmentID)
from Booking B
join Customer C on B.customerID = C.id
join BookingEquipment BE on B.id = BE.bookingID
group by C.zipcode

So far my linq 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() };

foreach (var s in statistics)
Console.WriteLine(s.Zipcode + ": " + s.NumberOfBookings);

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?

Thanks in advance :)

Answer Source

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