Lambda / LINQ查找多对多关系SQL表之间的关系

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.

其中一项任务是迭代遍历我所有的洗衣店，并显示每个洗衣店的洗衣机上有多少预定。我一直在寻找我的预订表和laundrymachine表之间相同的关系。

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?

但不知道该如何进行。如何在多对多关系中找到相同的行?

1 个解决方案

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

#1