Linq查询加载过慢求助：如何优化初始查询提升速度？

针对你当前LINQ查询加载耗时较长的问题，可从以下几个方向优化：

1. 调整查询顺序：先筛选再关联加载 #

原查询先Include所有关联表再执行筛选，会导致数据库先加载全量关联数据再过滤。优先执行Where筛选，让数据库先过滤掉不符合条件的记录，再加载关联数据，能大幅减少数据传输量：

// 先初始化基础查询，不立即Include所有关联
IQueryable<VaccinationClinic> clinics = _appDbContext.VaccinationClinics.AsQueryable();

// 先执行筛选逻辑
if (searchCriteria == SearchCriteria.Designated)
{
   clinics = clinics.Where(CheckDesignationIsActive(designationNumberOfDays));
   clinics = clinics.Where(CheckDesignationIsActivePart2(designationNumberOfDays));
   clinics = clinics.Where(sp => !sp.DedesignationDate.HasValue);
}

// 筛选完成后，再Include需要的关联表
clinics = clinics
                .Include(h => h.HealthBoard)
                .Include(c => c.ClinicType)
                .Include(v => v.VaccinationAvailableTo)
                .Include(p => p.AdministrativeLead).ThenInclude(pn => pn.Title)
                .Include(d => d.ResponsibleSupervisingClinician).ThenInclude(dr => dr.Title)
                .Include(d => d.ResponsibleSupervisingClinician).ThenInclude(dr => dr.ProfessionalBodyDetails)
                .Include(r => r.Designations)
                .Include(t => t.Training)
                .Include(a => a.AnnualFigures);

2. 重构重复的子查询逻辑 #

原筛选方法中多次重复调用sp.Designations.OrderByDescending(r => r.DesignatedAt).FirstOrDefault()，会生成冗余的SQL子查询，严重影响性能。可通过提取公共表达式避免重复计算：

private Expression<Func<VaccinationClinic, bool>> CheckDesignationIsActive(int desNumOfDays)
{
   return sp => 
   {
       // 提取最新的Designation，仅计算一次
       var latestDesignation = sp.Designations.OrderByDescending(r => r.DesignatedAt).FirstOrDefault();
       var expiryDate = latestDesignation.DesignatedAt.AddYears(latestDesignation.NumberOfYears);
       return expiryDate >= DateTime.Now;
   };
}

private Expression<Func<VaccinationClinic, bool>> CheckDesignationIsActivePart2(int desNumOfDays)
{
   return sp => 
   {
       var latestDesignation = sp.Designations.OrderByDescending(r => r.DesignatedAt).FirstOrDefault();
       var expiryDate = latestDesignation.DesignatedAt.AddYears(latestDesignation.NumberOfYears);
       // 合并逻辑：到期时间不在(当前时间, 当前时间+desNumOfDays]区间
       return !(expiryDate > DateTime.Now && expiryDate <= DateTime.Now.AddDays(desNumOfDays));
   };
}

更进一步，可将两个筛选条件合并为一个表达式，减少Where调用次数，简化SQL逻辑：

private Expression<Func<VaccinationClinic, bool>> CheckDesignationExpiryCondition(int desNumOfDays)
{
   return sp => 
   {
       var latestDesignation = sp.Designations.OrderByDescending(r => r.DesignatedAt).FirstOrDefault();
       var expiryDate = latestDesignation.DesignatedAt.AddYears(latestDesignation.NumberOfYears);
       // 合并原两个条件：到期时间>=当前时间，且不在预警区间内
       return expiryDate >= DateTime.Now && !(expiryDate > DateTime.Now && expiryDate <= DateTime.Now.AddDays(desNumOfDays));
   };
}
// 使用时只需调用一次Where
clinics = clinics.Where(CheckDesignationExpiryCondition(designationNumberOfDays));

3. 移除不必要的关联加载 #

逐一检查每个Include/ThenInclude关联的表，确认是否真的需要在业务逻辑中使用这些数据。例如，如果不需要展示ResponsibleSupervisingClinician的ProfessionalBodyDetails，就去掉对应的ThenInclude(dr => dr.ProfessionalBodyDetails)，减少数据库JOIN操作的开销。

4. 添加数据库索引优化 #

针对查询中的过滤、排序字段添加索引，加速数据库查询：

• 在Designations表的DesignatedAt字段添加降序索引，优化OrderByDescending(r => r.DesignatedAt)的性能
• 在VaccinationClinics表的DedesignationDate字段添加索引，加速!sp.DedesignationDate.HasValue的过滤
• 确保所有关联表的外键字段（如VaccinationClinics.HealthBoardId、VaccinationClinics.ClinicTypeId等）已添加索引，提升JOIN操作速度

5. 优化查询执行方式 #

• 保留AsSplitQuery()：该方法会将多关联的查询拆分为多个单表查询，避免因关联数据过多导致的笛卡尔积膨胀，适合关联表数据量大的场景
• 拆分查询：如果部分关联表数据使用频率低，可先查询核心的VaccinationClinic列表，再按需单独查询关联数据并在内存中组装，减少单次查询的数据量

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