.NET 6 API通用查询排序：布尔类型字段排序实现求助

Hey there! It sounds like you've already got a solid start with sorting by string/datetime fields in your .NET 6 API—extending this to boolean status fields (active/inactive) is totally doable with a reusable generic method that plays nicely with EF Core's query translation.

The Core Idea #

Instead of writing one-off OrderBy(u => u.Status == true) calls, we can build a generic extension method that lets you control whether to prioritize true (active) or false (inactive) values, and supports both ascending/descending sorting. The trick is converting the boolean value to an integer (1 for true, 0 for false) under the hood—this lets EF Core translate the logic directly to SQL, avoiding in-memory sorting which is crucial for performance with large datasets.

Generic Extension Method #

Add this static class to your project to create a reusable sorting method:

using System.Linq.Expressions;

public static class QueryableSortExtensions
{
    /// <summary>
    /// Sorts an IQueryable by a boolean field, with control over prioritizing true/false values and sort direction
    /// </summary>
    /// <typeparam name="T">Entity type</typeparam>
    /// <param name="query">Original query</param>
    /// <param name="boolSelector">Expression targeting the boolean field</param>
    /// <param name="prioritizeTrue">If true, true values come first; if false, false values come first</param>
    /// <param name="isDescending">If true, reverses the sort order</param>
    /// <returns>Sorted IQueryable</returns>
    public static IQueryable<T> OrderByBoolean<T>(this IQueryable<T> query, Expression<Func<T, bool>> boolSelector, bool prioritizeTrue, bool isDescending = false)
    {
        // Convert boolean to int (1 = true, 0 = false) for consistent sorting
        var intConversion = Expression.Lambda<Func<T, int>>(
            Expression.Condition(
                boolSelector.Body,
                Expression.Constant(1),
                Expression.Constant(0)
            ),
            boolSelector.Parameters
        );

        // Apply sorting based on priorities and direction
        if (isDescending)
        {
            return prioritizeTrue ? query.OrderByDescending(intConversion) : query.OrderBy(intConversion);
        }
        else
        {
            return prioritizeTrue ? query.OrderBy(intConversion) : query.OrderByDescending(intConversion);
        }
    }
}

How to Use It #

This method works with any boolean field on your entities. Here are common use cases matching your requirement:

1. Prioritize active (true) records, ascending order
   Equivalent to query.OrderBy(u => u.Status == true):

   query = query.OrderByBoolean(u => u.Status, prioritizeTrue: true);
   

   Translates to SQL like:

   ORDER BY CASE WHEN Status = 1 THEN 1 ELSE 0 END ASC
   

2. Prioritize inactive (false) records, ascending order
   Equivalent to query.OrderBy(u => u.Status == false):

   query = query.OrderByBoolean(u => u.Status, prioritizeTrue: false);
   

   This will list all inactive records first, then active ones.

3. Chain with existing sorting
   Combine with your existing name/created time sorting for multi-level ordering:

   // First sort by active status, then by created time descending
   query = query.OrderByBoolean(u => u.Status, prioritizeTrue: true)
                .ThenByDescending(u => u.CreatedTime);
   

Integrate with Frontend Sort Params #

If you're accepting sort parameters from the frontend (e.g., sortBy=status and sortDirection=desc), you can wrap this in a broader sorting method to handle all your fields:

public static IQueryable<YourEntity> ApplyDynamicSort(this IQueryable<YourEntity> query, string sortBy, string sortDirection)
{
    var isDescending = string.Equals(sortDirection, "desc", StringComparison.OrdinalIgnoreCase);
    
    return sortBy.ToLower() switch
    {
        "name" => isDescending ? query.OrderByDescending(u => u.Name) : query.OrderBy(u => u.Name),
        "createdtime" => isDescending ? query.OrderByDescending(u => u.CreatedTime) : query.OrderBy(u => u.CreatedTime),
        "status" => query.OrderByBoolean(u => u.Status, prioritizeTrue: true, isDescending),
        _ => query.OrderByDescending(u => u.CreatedTime) // Default sort
    };
}

Now you can call it directly with frontend inputs:

query = query.ApplyDynamicSort(request.SortBy, request.SortDirection);

Key Benefits #

• Reusable: Works with any boolean field in your entities, not just Status.
• EF Core Friendly: Translates directly to SQL, so sorting happens at the database level (no in-memory filtering).
• Flexible: Controls both priority of true/false and sort direction in one method.

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