I’m working on a method that retrieves a list of Student entities from a database based on several optional filtering and sorting criteria. The query is quite complex due to the following reasons:

Conditional Filtering:

Filtering by DivisionId, UserId, SiteId, StudentCategory, Status, and other fields depending on whether parameters are provided.
Some filters are combined using OR conditions, which complicates readability and maintainability.

Sorting:

Sorting by fields like EnrollmentNumber, StartDate, or Status, with support for ascending/descending order.
For EnrollmentNumber, I have a custom comparison logic that handles prefixed strings like “ABC-123”.

Pagination:

Skipping a specific number of records and limiting the results to a given page size.
Complex Joins and Includes:

The query involves several Include and ThenInclude calls to load related data such as StudentInfo, Statuses, and Enrollment.

My primary concerns are:

Performance: The query could be slow due to the complexity of filtering and sorting.

Readability: The code is getting hard to maintain and understand.

Optimization: I’m uncertain if I’m using the best practices for query building and executing.

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

These are the methods used:

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