Lambda / LINQ find relations between multiple many-to-many relationship SQL tables

Question

I'm working on a school project, where the objective is to create a system which can handle multiple laundries with the appropriate features like making a reservation for a laundry machine, user handling etc.

I have the following design:

One of the assignments is to iterate through all of my laundries and show how many reservations have been made on all of the laundry machines in each laundry. I'm stuck trying to find identical relationship between my reservation table and laundrymachine table.

I have the following code:

var queryList = (from laundry in _db.Laundries
                 join laundryMachine in _db.LaundryMachines on laundry.LaundryID equals laundryMachine.LaundryID
                 join res in _db.Reservations on laundryMachine.Reservations.Where(x => x.LaundryMachines.Select(z => z.MachineID) == res.MachineID)
                 select laundry).ToList();

But not sure how to proceed. How do I find identical rows in a many-to-many relationship?

Answer 1

Your query could be reduced to just:

var queryList = _db.Laundries
  .Include(l=>l.LaundryMachines)
  .Include(l=>l.LaundryMachines.Select(lm=>lm.Reservations))
  .ToList();

As for finding out how many reservations is at each laundry:

var result=_db.Laundries
  .Select(l=> new {
    Laundry=l,
    ReservationCount=l.LaundryMachines.Sum(lm=>lm.Reservations.Count())
    });

or if you just want to use the original query result:

foreach(var l in queryList)
{
   Console.WriteLine("{0} has {1} reservations",
     l.LaundryName,
     l.LaundryMachines.Sum(lm=>lm.Reservations.Count()));
}