如何优化并简化多条件学生数据查询的逻辑？

查询逻辑复杂点 #

条件筛选 #

根据DivisionId、UserId、SiteId、StudentCategory、Status等参数是否存在进行筛选，部分筛选条件采用OR组合，降低了代码可读性与可维护性。

排序 #

支持按EnrollmentNumber、StartDate、Status字段进行升序/降序排序，其中EnrollmentNumber需处理如“ABC-123”这类带前缀字符串的自定义比较逻辑。

分页 #

支持跳过指定数量记录并限制每页结果条数。

复杂关联查询 #

查询中包含多个Include与ThenInclude调用，用于加载StudentInfo、Statuses、Enrollment等关联数据。

核心顾虑 #

• 性能：复杂的筛选与排序可能导致查询速度较慢。
• 可读性：代码逐渐难以维护与理解。
• 优化：不确定是否遵循了查询构建与执行的最佳实践。

实现代码 #

public async Task<Result<List<Student>>> GetAll(GetStudentsQueryParametersDto queryParameters)
{
    try
    {
        IQueryable<Student> query = _dbContext
            .Students.Where(s => s.Enrollment != null && s.Enrollment.IsGroupEnrollment == false)
            .Include(s => s.StudentInfo)
            .Include(s => s.Enrollment);

        if (queryParameters.ClassId.HasValue)
        {
            query = query.Where(s =>
                (s.Enrollment != null && s.Enrollment.ClassId == queryParameters.ClassId)
                || (
                    s.StudentInfo != null
                    && s.StudentInfo.StudentCategory != null
                    && queryParameters.StudentCategories.Contains(s.StudentInfo.StudentCategory)
                )
            );
        }

        if (queryParameters.TeacherId.HasValue)
        {
            query = query.Where(s => s.Enrollment != null && s.Enrollment.TeacherId == queryParameters.TeacherId.Value);
        }

        if (queryParameters.SchoolId.HasValue)
        {
            query = query.Where(s => s.StudentInfo != null && s.StudentInfo.SchoolId == queryParameters.SchoolId.Value);
        }

        if (queryParameters.View != null)
        {
            query = FilterByDate(query, queryParameters.View);
        }

        if (queryParameters.EnrollmentType != null)
        {
            query = FilterByEnrollmentType(query, queryParameters.EnrollmentType);
        }

        query = query
            .Include(s => s.StudentInfo)
            .ThenInclude(i => i!.Statuses)
            .ThenInclude(s => s.StatusReason)
            .Include(s => s.StudentInfo)
            .ThenInclude(i => i!.PreferredSchools)
            .Include(s => s.FocusSubjects);

        if (queryParameters.Status.Count != 0)
        {
            query = FilterByStatus(query, queryParameters.Status);
        }

        var studentsList = new List<Student>();
        if (queryParameters.Sort != null && queryParameters.Sort.ToLower() != "enrollmentnumber")
        {
            var sortedQuery = ApplySorting(query, queryParameters.Sort, queryParameters.Direction);

            var paginatedQuery = sortedQuery.Skip(queryParameters.Offset ?? 0).Take(queryParameters.Limit ?? 10);

            studentsList = await paginatedQuery.ToListAsync();
        }
        else
        {
            studentsList = await query.ToListAsync();
            var orderedStudents = SortByEnrollmentNumber(studentsList, queryParameters.Direction);
            studentsList = orderedStudents.Skip(queryParameters.Offset ?? 0).Take(queryParameters.Limit ?? 10).ToList();
        }

        return Result<List<Student>>.Success(studentsList);
    }
    catch (BadRequestException e)
    {
        _logger.LogError(
            e,
            "Failed to get all students with pagination and parameters: {queryParameters}",
            queryParameters
        );
        return Result<List<Student>>.Failure(Errors.BadRequest(e.Message));
    }
    catch (Exception e)
    {
        _logger.LogError(
            e,
            "Failed to get all students with pagination and parameters: {queryParameters}",
            queryParameters
        );
        return Result<List<Student>>.Failure(Errors.InternalServerError);
    }
}

辅助方法 #

private static IQueryable<Student> ApplySorting(IQueryable<Student> query, string? sortField, string? sortDirection)
{
    var isDescending = sortDirection?.Equals("desc", StringComparison.OrdinalIgnoreCase) ?? false;

    return sortField?.ToLower() switch
    {
        "startdate" => isDescending
            ? query.OrderByDescending(s => s.StudentInfo!.StartDate)
            : query.OrderBy(s => s.StudentInfo!.StartDate),
        "status" => isDescending
            ? query.OrderByDescending(s =>
                s.StudentInfo!.Statuses.OrderByDescending(st => st.CreatedAt).FirstOrDefault()!.Status
            )
            : query.OrderBy(s =>
                s.StudentInfo!.Statuses.OrderByDescending(st => st.CreatedAt).FirstOrDefault()!.Status
            ),
        _ => isDescending
            ? query.OrderByDescending(s => s.StudentInfo!.StartDate)
            : query.OrderBy(s => s.StudentInfo!.StartDate)
    };
}

private static IOrderedEnumerable<Student> SortByEnrollmentNumber(
    IEnumerable<Student> studentsList,
    string? queryParametersDirection
)
{
    if (!string.IsNullOrEmpty(queryParametersDirection) && queryParametersDirection.ToLower() == "desc")
    {
        return studentsList.OrderByDescending(s => s.Enrollment!.EnrollmentNumber, new EnrollmentNumberComparer());
    }

    return studentsList.OrderBy(s => s.Enrollment!.EnrollmentNumber, new EnrollmentNumberComparer());
}

public class EnrollmentNumberComparer : IComparer<string>
{
    public int Compare(string? enrollmentNumber1, string? enrollmentNumber2)
    {
        ArgumentNullException.ThrowIfNull(enrollmentNumber1);
        ArgumentNullException.ThrowIfNull(enrollmentNumber2);

        if (enrollmentNumber1 == enrollmentNumber2)
            return 0;

        var enrollmentNumber1Parts = enrollmentNumber1.Split('-');
        var enrollmentNumber2Parts = enrollmentNumber2.Split('-');
        var enrollmentNumber1Prefix = enrollmentNumber1Parts[0];
        var enrollmentNumber2Prefix = enrollmentNumber2Parts[0];

        var prefixComparisonResult = enrollmentNumber1Prefix.CompareTo(enrollmentNumber2Prefix);

        if (prefixComparisonResult != 0)
        {
            return prefixComparisonResult;
        }
        else
        {
            return int.Parse(enrollmentNumber1Parts[1]).CompareTo(int.Parse(enrollmentNumber2Parts[1]));
        }
    }
}

内容的提问来源于stack exchange，提问作者Özgür