ToList()性能优化：AsQueryable转List耗时超2-3分钟求解决方案

现有两个查询本身执行速度较快，但执行return documents.ToList()时耗时超2-3分钟，需将该操作压缩至1秒内完成。相关原始C#代码如下：

var apiUser = ApiCall.InstanceAPIUser();
                var allusers = (await apiUser.UsersGetAllAsync()).Data.ToList();
                IQueryable<AllDocumentDetails> documents = null;
                var currentRut = allCompanies.FirstOrDefault().Rut;
                var documentsSql = acompDbContext.Documents.ToList();

                var rutList = allCompanies.Select(c => c.Rut).ToList();
                var query1 = from d1 in documentsSql
                             join c in rutList on d1.DocRecep equals c
                             join da in acompDbContext.DocumentAssignations on d1.DocumentId equals da.DocumentId
                             join dau in acompDbContext.DocumentAssignationAssignedUsers on da.DocumentAssignationId equals dau.DocumentAssignationId
                             join drp in acompDbContext.DocumentReceivedProcesses on d1.DocumentId equals drp.DocumentId into drpGroup
                             from drp in drpGroup.DefaultIfEmpty()
                             join dc in acompDbContext.DocumentComments on d1.DocumentId equals dc.DocumentId into dcGroup
                             from dc in dcGroup.DefaultIfEmpty()
                             let assignedUser = acompDbContext.DocumentAssignationAssignedUsers
                                                  .Where(c => c.DocumentAssignationId == da.DocumentAssignationId && da.InProcess == true && d1.DocumentId == da.DocumentId)
                                                  .Select(x => x.UserId)
                                                  .Distinct()
                                                  .ToList()
                             select new AllDocumentDetails
                             {
                                 DocumentId = Convert.ToInt32(d1.DocumentId), // Include the unique identifier
                                 AssignedUser = allusers.Where(x => assignedUser.Contains(x.Id)).Select(x => new AssignedUser { UserId = x.Id, Email = x.Email }).ToList(),
                                 DocumentType = d1.DocumentTypeId,
                                 DocSeries = d1.Serie,
                                 DocNumber = d1.Nro,
                                 DocRecep = d1.DocRecep,
                                 RutTaxId = d1.EmitingRUC,
                                 IssuedDate = d1.FchEmis,
                                 DueDate = d1.ExpireDate,
                                 CompanyName = d1.LegalName,
                                 ApprovalDate = drp.ExpiringApprovalDate,
                                 State = Convert.ToInt32(drp.Status),
                                 Claim = dc != null && dc.Claim == true,
                                 Comments = dc != null && dc.DocumentCommentId != 0,
                                 Pause = drp.Status.ToString() == "5",
                                 ApprovalType = Convert.ToInt32(da.ApprovalType),
                                 ExpiredApproval = drp.ExpiredApproval
                             };

                var query2 = from d1 in documentsSql
                             join c in rutList on d1.DocRecep equals c
                             join drp in acompDbContext.DocumentReceivedProcesses on d1.DocumentId equals drp.DocumentId into drpGroup
                             from drp in drpGroup.DefaultIfEmpty()
                             join dc in acompDbContext.DocumentComments on d1.DocumentId equals dc.DocumentId into dcGroup
                             from dc in dcGroup.DefaultIfEmpty()
                             where !acompDbContext.Documents
                                      .Any(d =>
                                          d.DocumentId == d1.DocumentId &&
                                          rutList.Contains(d.DocRecep) &&
                                          d.DocumentAssignations != null &&
                                          d.DocumentAssignations.Any(da => da.DocumentId == d.DocumentId))
                             select new AllDocumentDetails
                             {
                                 DocumentId = Convert.ToInt32(d1.DocumentId), // Include the unique identifier
                                 AssignedUser = new List<AssignedUser>(),
                                 DocumentType = d1.DocumentTypeId,
                                 DocSeries = d1.Serie,
                                 DocNumber = d1.Nro,
                                 DocRecep = d1.DocRecep,
                                 RutTaxId = d1.EmitingRUC,
                                 IssuedDate = d1.FchEmis,
                                 DueDate = d1.ExpireDate,
                                 CompanyName = d1.LegalName,
                                 ApprovalDate = drp.ExpiringApprovalDate,
                                 State = Convert.ToInt32(drp.Status),
                                 Claim = dc != null && dc.Claim == true,
                                 Comments = dc != null && dc.DocumentCommentId != 0,
                                 Pause = drp.Status.ToString() == "5",
                                 ApprovalType = null,
                                 ExpiredApproval = drp.ExpiredApproval
                             };

                 documents = query1.Union(query2)
                                 .GroupBy(x => x.DocumentId) // Group by the unique identifier
                                 .Select(g => g.First())
                                 .AsQueryable<AllDocumentDetails>();

• 提前加载全量Documents数据：var documentsSql = acompDbContext.Documents.ToList()直接把所有Document数据拉到内存，后续关联操作全在内存中执行，数据量一大就会暴增耗时。
• 内存中执行多表关联：query1和query2都是基于内存中的documentsSql做关联，而非让数据库执行关联查询，数据库的查询优化器无法发挥作用。
• 嵌套查询重复执行：query1中的assignedUser每次循环都去数据库查询一次，造成N+1查询问题。
• Union后内存分组去重：把两个内存查询结果Union后再分组，内存操作效率远低于数据库层面的去重。
• 不必要的客户端转换：多处ToList()提前触发查询，导致后续无法组合成高效的SQL语句。

1. 移除提前加载全量Documents，改为数据库端查询 #

直接使用acompDbContext.Documents作为查询源，让EF Core将整个查询转换为SQL在数据库执行，利用数据库的索引和查询优化能力。

2. 消除N+1查询，将嵌套查询改为关联查询 #

把query1中获取assignedUser的逻辑改为数据库端的关联查询，一次性获取所有需要的用户ID，避免循环查询。

3. 合并查询逻辑，避免Union后内存分组 #

将query2的条件整合到主查询中，用左连接判断有无分配记录的情况，避免两次内存查询后再Union和分组。

4. 提前加载用户数据并构建字典，减少内存遍历 #

将allusers转换为以Id为Key的字典，后续查询用户时直接通过Key取值，替代Where(x => assignedUser.Contains(x.Id))的O(n)遍历。

5. 避免不必要的类型转换和字符串操作 #

将drp.Status.ToString() == "5"改为直接比较数值（如果Status是数值类型），减少字符串转换开销；若DocumentId本身是int类型，移除Convert.ToInt32(d1.DocumentId)的多余转换。

var apiUser = ApiCall.InstanceAPIUser();
var allusers = (await apiUser.UsersGetAllAsync()).Data.ToList();
// 构建用户字典，实现O(1)时间复杂度查找
var userDict = allusers.ToDictionary(u => u.Id, u => new AssignedUser { UserId = u.Id, Email = u.Email });

var rutList = allCompanies.Select(c => c.Rut).ToList();

// 合并查询逻辑，所有关联操作在数据库端执行
var documentsQuery = from d in acompDbContext.Documents
                     where rutList.Contains(d.DocRecep)
                     // 左连接接收流程表
                     join drp in acompDbContext.DocumentReceivedProcesses on d.DocumentId equals drp.DocumentId into drpGroup
                     from drp in drpGroup.DefaultIfEmpty()
                     // 左连接评论表
                     join dc in acompDbContext.DocumentComments on d.DocumentId equals dc.DocumentId into dcGroup
                     from dc in dcGroup.DefaultIfEmpty()
                     // 左连接分配记录表，判断是否存在分配
                     join da in acompDbContext.DocumentAssignations on d.DocumentId equals da.DocumentId into daGroup
                     from da in daGroup.DefaultIfEmpty()
                     // 左连接分配用户表，仅关联InProcess为true的分配记录
                     join dau in acompDbContext.DocumentAssignationAssignedUsers on 
                         new { da.DocumentAssignationId, IsInProcess = da.InProcess == true } equals 
                         new { dau.DocumentAssignationId, IsInProcess = true } into dauGroup
                     from dau in dauGroup.DefaultIfEmpty()
                     // 按文档ID分组，避免重复数据
                     group new { d, drp, dc, dau, da } by d.DocumentId into g
                     select new AllDocumentDetails
                     {
                         DocumentId = (int)g.Key, // 若DocumentId不是int类型，保留Convert.ToInt32
                         AssignedUser = g.Where(x => x.dau != null)
                                         .Select(x => x.dau.UserId)
                                         .Distinct()
                                         .Where(userId => userDict.ContainsKey(userId))
                                         .Select(userId => userDict[userId])
                                         .ToList(),
                         DocumentType = g.First().d.DocumentTypeId,
                         DocSeries = g.First().d.Serie,
                         DocNumber = g.First().d.Nro,
                         DocRecep = g.First().d.DocRecep,
                         RutTaxId = g.First().d.EmitingRUC,
                         IssuedDate = g.First().d.FchEmis,
                         DueDate = g.First().d.ExpireDate,
                         CompanyName = g.First().d.LegalName,
                         ApprovalDate = g.First().drp?.ExpiringApprovalDate,
                         State = g.First().drp != null ? (int)g.First().drp.Status : default,
                         Claim = g.First().dc != null && g.First().dc.Claim,
                         Comments = g.First().dc != null && g.First().dc.DocumentCommentId != 0,
                         Pause = g.First().drp != null && g.First().drp.Status == 5, // 假设Status是数值类型
                         ApprovalType = g.First().da != null ? (int?)g.First().da.ApprovalType : null,
                         ExpiredApproval = g.First().drp?.ExpiredApproval
                     };

// 异步执行数据库查询，仅触发一次SQL请求
var documents = await documentsQuery.ToListAsync();
return documents;

• 添加数据库索引：给Documents.DocRecep、DocumentAssignations.DocumentId、DocumentAssignationAssignedUsers.DocumentAssignationId、DocumentReceivedProcesses.DocumentId、DocumentComments.DocumentId这些关联字段添加索引，加速数据库查询。
• 分页查询：如果返回数据量极大，添加分页逻辑，避免一次性加载过多数据到内存。
• 监控SQL执行计划：使用EF Core的日志功能查看生成的SQL语句，确认数据库是否使用了索引，进一步优化查询。

内容的提问来源于stack exchange，提问作者Shashank Singh