ASP.NET Core REST API：如何将排序过滤信息从API层传至DAL？

Great question! Let's walk through how to integrate sorting (?Sort=title,-rating) and field filtering (?Fields=id,title,genres(name)) into your existing pipeline without breaking separation of concerns. Here's a step-by-step approach tailored to your architecture:

1. Define a Reusable Query Parameters Model #

First, create a shared model to capture the sorting and filtering parameters from the request query string. This keeps your controller methods clean and consistent:

public class QueryParameters
{
    // Example: "title,-rating" → sort by title ascending, then rating descending
    public string? Sort { get; set; }
    
    // Example: "id,title,genres(name)" → return only these fields (including nested genre names)
    public string? Fields { get; set; }

    // Optional: Add pagination params if you need them later
    public int PageNumber { get; set; } = 1;
    public int PageSize { get; set; } = 10;
}

2. Bind Parameters in Your Controller #

In your API controller, accept this model as a [FromQuery] parameter. Then package it into your Query object to pass down the pipeline:

[HttpGet]
public async Task<IActionResult> GetMovies([FromQuery] QueryParameters parameters)
{
    // Validate parameters first (use FluentValidation for clean validation logic)
    var validationResult = await _queryParamsValidator.ValidateAsync(parameters);
    if (!validationResult.IsValid)
    {
        return BadRequest(validationResult.Errors);
    }

    // Pass parameters into your Query object
    var query = new GetMoviesQuery(parameters);
    
    // Execute the query via your handler (using MediatR here, adjust to your implementation)
    var movieDtos = await _mediator.Send(query);

    // Optional: Apply field filtering at the API layer (if not handled in DAL)
    var filteredResponse = FilterResponseFields(movieDtos, parameters.Fields);

    return Ok(filteredResponse);
}

3. Embed Parameters in Your Query Object #

Update your Query class to include the QueryParameters so it travels to the Query Handler:

// Assuming you're using MediatR; adjust to your IQuery/IRequest equivalent
public class GetMoviesQuery : IRequest<IEnumerable<MovieDto>>
{
    public QueryParameters Parameters { get; }

    public GetMoviesQuery(QueryParameters parameters)
    {
        Parameters = parameters;
    }
}

4. Process Sorting/Filtering in the Query Handler #

This is where you translate the string parameters into executable database logic. For sorting, use expression trees (or a library like DynamicLINQ to simplify). For filtering, you can either project only needed fields in the DAL (better for performance) or handle it later in the API layer.

Example Handler with EF Core: #

public class GetMoviesQueryHandler : IRequestHandler<GetMoviesQuery, IEnumerable<MovieDto>>
{
    private readonly AppDbContext _dbContext;

    public GetMoviesQueryHandler(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<IEnumerable<MovieDto>> Handle(GetMoviesQuery request, CancellationToken cancellationToken)
    {
        var movieQuery = _dbContext.Movies.AsQueryable();

        // 1. Apply sorting
        if (!string.IsNullOrEmpty(request.Parameters.Sort))
        {
            movieQuery = ApplySorting(movieQuery, request.Parameters.Sort);
        }

        // 2. Apply field projection (if handling filtering in DAL)
        var dtoQuery = ApplyFieldProjection(movieQuery, request.Parameters.Fields);

        return await dtoQuery.ToListAsync(cancellationToken);
    }

    // Helper: Dynamic sorting with expression trees
    private IQueryable<Movie> ApplySorting(IQueryable<Movie> query, string sortString)
    {
        var sortClauses = sortString.Split(',');
        foreach (var clause in sortClauses)
        {
            var isDescending = clause.StartsWith('-');
            var fieldName = isDescending ? clause[1..] : clause;

            // Build expression tree for sorting
            var parameter = Expression.Parameter(typeof(Movie), "m");
            var property = Expression.Property(parameter, fieldName);
            var sortLambda = Expression.Lambda(property, parameter);

            var sortMethod = isDescending 
                ? typeof(Queryable).GetMethod("OrderByDescending", new[] { typeof(IQueryable<Movie>), typeof(Expression<Func<Movie, object>>) })
                : typeof(Queryable).GetMethod("OrderBy", new[] { typeof(IQueryable<Movie>), typeof(Expression<Func<Movie, object>>) });

            query = (IQueryable<Movie>)sortMethod!.Invoke(null, new object[] { query, sortLambda })!;
        }
        return query;
    }

    // Helper: Dynamic field projection (simplified for nested fields like genres(name))
    private IQueryable<MovieDto> ApplyFieldProjection(IQueryable<Movie> query, string? fields)
    {
        if (string.IsNullOrEmpty(fields))
        {
            // Return full DTO if no fields specified
            return query.Select(m => new MovieDto
            {
                Id = m.Id,
                Title = m.Title,
                Rating = m.Rating,
                Genres = m.Genres.Select(g => new GenreDto { Name = g.Name })
            });
        }

        // Parse fields (split top-level and nested)
        var fieldList = fields.Split(',').Select(f => f.Trim()).ToList();
        var includeNested = fieldList.Any(f => f.Contains('('));

        // Build projection dynamically (simplified example; use AutoMapper's ProjectTo for complex cases)
        return query.Select(m => new MovieDto
        {
            Id = fieldList.Contains("id", StringComparer.OrdinalIgnoreCase) ? m.Id : default,
            Title = fieldList.Contains("title", StringComparer.OrdinalIgnoreCase) ? m.Title : default,
            Genres = includeNested && fieldList.Any(f => f.StartsWith("genres(", StringComparer.OrdinalIgnoreCase))
                ? m.Genres.Select(g => new GenreDto { Name = g.Name })
                : null
        });
    }
}

5. Optional: Field Filtering at the API Layer #

If you prefer to fetch full DTOs from the DAL and filter fields before sending the response (good for flexibility with nested data), use a helper method like this:

private IEnumerable<object> FilterResponseFields(IEnumerable<MovieDto> dtos, string? fields)
{
    if (string.IsNullOrEmpty(fields)) return dtos;

    var fieldMap = fields.Split(',')
        .Select(f => f.Trim())
        .ToDictionary(f => f.Split('(')[0].ToLower(), f => f);

    return dtos.Select(dto =>
    {
        var response = new ExpandoObject() as IDictionary<string, object>;

        // Handle top-level fields
        foreach (var prop in typeof(MovieDto).GetProperties())
        {
            if (fieldMap.ContainsKey(prop.Name.ToLower()))
            {
                var fieldDef = fieldMap[prop.Name.ToLower()];
                if (!fieldDef.Contains('('))
                {
                    response[prop.Name] = prop.GetValue(dto)!;
                }
                // Handle nested fields (e.g., genres(name))
                else
                {
                    var nestedField = fieldDef.Split('(', ')')[1].Trim();
                    var nestedCollection = prop.GetValue(dto) as IEnumerable<object>;
                    if (nestedCollection != null)
                    {
                        var nestedType = prop.PropertyType.GetGenericArguments()[0];
                        var nestedProp = nestedType.GetProperty(nestedField, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                        if (nestedProp != null)
                        {
                            response[prop.Name] = nestedCollection.Select(item =>
                            {
                                var nestedObj = new ExpandoObject() as IDictionary<string, object>;
                                nestedObj[nestedField] = nestedProp.GetValue(item)!;
                                return nestedObj;
                            });
                        }
                    }
                }
            }
        }

        return response;
    });
}

Key Tips for Production #

• Validate Parameters: Use libraries like FluentValidation to ensure sort/filter fields match your entity properties (prevents invalid queries and potential injection risks).
• Simplify with Libraries: Use DynamicLINQ to replace the manual expression tree sorting code (e.g., query.OrderBy(request.Parameters.Sort.Replace('-', ' '))).
• Performance: Always prefer filtering/projection in the DAL (EF Core translates it to SQL) to minimize data transfer from the database.
• Caching: If your data doesn't change often, cache the filtered/sorted results to reduce database load.

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