c# - 如何使我的多对多查询更快？

Question

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

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;
    }

我尝试使用代码内秒表并推断出 foreach 是罪魁祸首。我能否在不借助于编写存储过程的情况下获得更好的性能？现在的情况是，获取 400 行导致等待 6-9 秒，这是 Not Acceptable 。

Answer 1

更改您的 loModel 以连接所有三个表。然后，您将只需要一个 foreach。