如何更快地进行多对多查询？

A page in my application serves to display company 'problems' that have arisen in the past. These problems are submitted to the database by a single User, but one or many other users are assigned to deal with the problem, and vice-versa, a single user can be assigned to deal with one or many problems.

我的应用程序中的一个页面用于显示过去出现的公司“问题”。这些问题由单个用户提交给数据库，但是分配了一个或多个其他用户来处理问题，反之亦然，可以指派单个用户来处理一个或多个问题。

This is what the relationship looks like. My main trouble with this is that when issuing a getAllProblems() request, I have to go through all the users assigned to problems, which brings me to an ugly foreach within a foreach. My code looks like this:

这就是这种关系的样子。我遇到的主要问题是，当发出getAllProblems（）请求时，我必须通过分配给问题的所有用户，这使我在foreach中遇到了一个丑陋的foreach。我的代码如下所示：

public HttpResponseMessage GetProblems(String ClientUserHash) {
         this.ClientUserHash = ClientUserHash;

          HttpResponseMessage loResponse;

          if (!CheckClientHash()) {
              SetResponseToBad(out loResponse);
          } else {
              List<Int64> loFilteredObjects = PermissionsHelper.UserObjects(ClientUserID);
              var loModel = (from p in MeridianDatabase.Problems                                 
                             join o in MeridianDatabase.Objects on p.ObjectID equals o.ObjectID
                             where loFilteredObjects.Contains(o.ObjectID) && ((p.Archive == false) || (p.Archive == null))
                             select new ProblemsModel {
                                 ProblemID =     p.ProblemID,
                                 Description =   p.Description,
                                 Comment =       p.Comment,
                                 Status =        p.Status,
                                 Picture =       p.Picture,
                                 DateOpen =      p.DateOpen,
                                 DateClosed =    p.DateClosed,
                                 CategoryID =    p.CategoryID,
                                 CategoryName =  p.ProblemCategory.Name,
                                 ObjectID =      p.ObjectID,
                                 ObjectName =    p.Object.Name,
                                 EmployeeID =    p.EmployeeID,
                                 FullName =      p.Employee.Candidate.FirstName + " " + p.Employee.Candidate.LastName,
                                 CompanyName =   p.Object.Company.Name
                             }).ToList();
              foreach (ProblemsModel toProblem in loModel) {
                  var toDbUsers = MeridianDatabase.Problems.Where(x => x.ProblemID == toProblem.ProblemID).FirstOrDefault().Users;
                  if (toDbUsers.Count > 0) {
                      toProblem.ProblemUsers = new List<string>();
                      List<UsersModel> toUsersList = new List<UsersModel>();
                      foreach (User toUser in toDbUsers) {
                          toProblem.ProblemUsers.Add(String.Format("{0}-{1}", toUser.UserID, toUser.Username));
                          UsersModel toUserModel = new UsersModel() {
                              UserID = toUser.UserID,
                              Username = toUser.Username,
                              Password = toUser.Password,
                              Email = toUser.Email,
                              UserGroupsID = toUser.PermissionLevelID,
                              IsAdmin = toUser.IsAdmin,
                              LanguageID = toUser.LanguageID,
                              Name = toUser.Nickname
                          };
                          toUsersList.Add(toUserModel);
                      }
                      toProblem.Users = toUsersList;
                  }
              }
              loResponse = Request.CreateResponse(HttpStatusCode.OK, loModel);
          }
        return loResponse;
    }

I've tried using the in-code stopwatch and inferred that the foreach is the culprit. Can I get better performance without resorting to writing stored procedures? The way things are now, fetching 400 rows results in a 6-9 seconds wait, which is unacceptable.

我尝试过使用内码秒表并推断出foreach是罪魁祸首。如果不诉诸编写存储过程，我可以获得更好的性能吗？现在的方式，获取400行导致等待6-9秒，这是不可接受的。

2 个解决方案

#1

Change your loModel to join all three tables. Then, you will need one foreach only.

更改您的loModel以加入所有三个表。然后，你只需要一个foreach。

#2

Try something like this:

尝试这样的事情：

from client_uo in UserObjects
join client_obj in Objects on client_uo.ObjectID == client_obj.ObjectID
join p in Problems on p.ObjectID == clt_obj.ObjectID
join pu in ProblemUsers on p.ProblemID == pu.ProblemID
join assignee in Users on pu.UserID == assignee.UserID
where client_obj.UserID == ClientUserID

that should produce a multi join that gets you from ClientUserID to Client-UserObjects to ProblemObjects to Problems to ProblemUsers (assignees?) to Users, with all the data available at one go. You may want to group it together, either by object, by problem, or by assigned user, depending on the display you're trying to produce.

应该产生一个多连接，它可以让你从ClientUserID到Client-UserObjects到ProblemObjects再到ProblemUsers（assignees？）到用户的问题，一次性获得所有可用的数据。您可能希望按对象，按问题或由分配的用户将它们组合在一起，具体取决于您尝试生成的显示。

#1