.NET Core API可选查询参数与分页过滤的参数化实现咨询

嘿，这个问题我之前也碰到过！手动拼接SQL确实有安全风险，而且维护起来头疼，不过有几个非常靠谱的方案能解决你的问题，既保证参数化安全，又能处理可选过滤和分页，还不影响性能。

方案1：用EF Core LINQ动态构建查询（首推） #

如果你在用Entity Framework Core（.NET Core API项目里大概率是），这绝对是最省心的方案。EF会自动帮你生成参数化SQL，彻底避免注入问题，而且代码可读性拉满，维护起来超简单。

首先，假设你的OptionalSearchParams模型是这样的（如果还没定义的话）：

public class OptionalSearchParams
{
    public string? Field1Contains { get; set; }
    public string? Field1Equals { get; set; }
    public string? Field2Contains { get; set; }
    public string? Field2Equals { get; set; }
    // 其他需要过滤的字段...
}

然后修改你的API方法，用LINQ逐步构建查询：

[HttpGet]
[Authorize(AuthenticationSchemes = AuthSchemes)]
[Route("items")]
public async Task<IActionResult> GetPagedItems(
    [FromQuery] PageParameters pageParameters,
    [FromQuery] OptionalSearchParams searchParameters)
{
    // 从DbSet初始化基础查询
    var query = _context.SomeTable.AsQueryable();

    // 处理Field1的过滤：Contains优先级高于Equals
    if (!string.IsNullOrEmpty(searchParameters.Field1Contains))
    {
        query = query.Where(x => x.Field1.Contains(searchParameters.Field1Contains));
    }
    else if (!string.IsNullOrEmpty(searchParameters.Field1Equals))
    {
        query = query.Where(x => x.Field1 == searchParameters.Field1Equals);
    }

    // 同理处理Field2的过滤逻辑
    if (!string.IsNullOrEmpty(searchParameters.Field2Contains))
    {
        query = query.Where(x => x.Field2.Contains(searchParameters.Field2Contains));
    }
    else if (!string.IsNullOrEmpty(searchParameters.Field2Equals))
    {
        query = query.Where(x => x.Field2 == searchParameters.Field2Equals);
    }

    // 分页处理：先排序，再跳过分页偏移量，取指定条数
    var pagedItems = await query
        .OrderBy(x => x.Field) // 替换成你实际的排序字段
        .Skip((pageParameters.PageNumber - 1) * pageParameters.PageSize)
        .Take(pageParameters.PageSize)
        .ToListAsync();

    // 如果需要给前端返回总条目数（用于分页控件），单独查询计数
    var totalCount = await query.CountAsync();

    return Ok(new { Items = pagedItems, TotalCount = totalCount });
}

为啥这个方案好？ #

• 绝对安全：EF Core会自动把过滤值转换成SQL参数，完全杜绝SQL注入。
• 动态生成查询：只有当参数存在时，才会添加对应的WHERE子句，不会生成冗余条件。
• 性能拉满：生成的SQL和你手动拼接的几乎一致，分页用Skip/Take会转换成SQL标准的OFFSET/FETCH，不会全表扫描。
• 维护简单：新增过滤字段只需要加一段类似的Where判断，比嵌套CASE简单太多。

方案2：参数化存储过程（如果必须用数据库层逻辑） #

如果因为项目要求必须用存储过程，那也可以用参数化的方式处理，绝对不能手动拼接SQL。

先创建一个存储过程，接受所有可选参数，用条件判断处理优先级：

CREATE PROCEDURE GetPagedItems
    @PageNumber INT,
    @PageSize INT,
    @Field1Contains NVARCHAR(MAX) = NULL,
    @Field1Equals NVARCHAR(MAX) = NULL,
    @Field2Contains NVARCHAR(MAX) = NULL,
    @Field2Equals NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;

    -- 查询分页数据
    SELECT 
        Field1, Field2 -- 替换成你的实际字段列表
    FROM SomeTable
    WHERE 
        -- Field1的过滤逻辑：Contains优先，都为空则跳过
        (
            (@Field1Contains IS NOT NULL AND Field1 LIKE '%' + @Field1Contains + '%')
            OR
            (@Field1Contains IS NULL AND @Field1Equals IS NOT NULL AND Field1 = @Field1Equals)
            OR
            (@Field1Contains IS NULL AND @Field1Equals IS NULL)
        )
        AND
        -- Field2的过滤逻辑同上
        (
            (@Field2Contains IS NOT NULL AND Field2 LIKE '%' + @Field2Contains + '%')
            OR
            (@Field2Contains IS NULL AND @Field2Equals IS NOT NULL AND Field2 = @Field2Equals)
            OR
            (@Field2Contains IS NULL AND @Field2Equals IS NULL)
        )
    ORDER BY Field -- 替换成你的排序字段
    OFFSET @Offset ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- 查询总条目数（可选，用于前端分页）
    SELECT COUNT(*) AS TotalCount
    FROM SomeTable
    WHERE 
        -- 重复上面的过滤条件，或者封装成CTE/表值函数复用
        (
            (@Field1Contains IS NOT NULL AND Field1 LIKE '%' + @Field1Contains + '%')
            OR
            (@Field1Contains IS NULL AND @Field1Equals IS NOT NULL AND Field1 = @Field1Equals)
            OR
            (@Field1Contains IS NULL AND @Field1Equals IS NULL)
        )
        AND
        (
            (@Field2Contains IS NOT NULL AND Field2 LIKE '%' + @Field2Contains + '%')
            OR
            (@Field2Contains IS NULL AND @Field2Equals IS NOT NULL AND Field2 = @Field2Equals)
            OR
            (@Field2Contains IS NULL AND @Field2Equals IS NULL)
        );
END

然后在API里调用这个存储过程：

var parameters = new SqlParameter[]
{
    new SqlParameter("@PageNumber", pageParameters.PageNumber),
    new SqlParameter("@PageSize", pageParameters.PageSize),
    new SqlParameter("@Field1Contains", (object)searchParameters.Field1Contains ?? DBNull.Value),
    new SqlParameter("@Field1Equals", (object)searchParameters.Field1Equals ?? DBNull.Value),
    new SqlParameter("@Field2Contains", (object)searchParameters.Field2Contains ?? DBNull.Value),
    new SqlParameter("@Field2Equals", (object)searchParameters.Field2Equals ?? DBNull.Value)
};

// 获取分页数据
var pagedItems = await _context.SomeTable
    .FromSqlRaw("EXEC GetPagedItems @PageNumber, @PageSize, @Field1Contains, @Field1Equals, @Field2Contains, @Field2Equals", parameters)
    .ToListAsync();

// 获取总条目数（如果存储过程返回两个结果集）
int totalCount = 0;
using (var reader = await _context.Database.ExecuteReaderAsync(new CommandDefinition(
    "EXEC GetPagedItems @PageNumber, @PageSize, @Field1Contains, @Field1Equals, @Field2Contains, @Field2Equals", 
    parameters)))
{
    // 读取第一个结果集（分页数据），如果不需要可以跳过
    _context.SomeTable.FromSqlReader(reader).ToList();
    // 切换到第二个结果集（总条数）
    reader.NextResult();
    if (reader.Read())
    {
        totalCount = reader.GetInt32(0);
    }
}

return Ok(new { Items = pagedItems, TotalCount = totalCount });

这个方案的缺点是维护起来不如LINQ方便，新增字段需要修改存储过程，但胜在完全参数化，没有注入风险。

额外小技巧 #

• 如果担心LIKE '%value%'的性能，可以给对应字段创建全文索引，然后用CONTAINS替代LIKE，查询速度会大幅提升，尤其是大表。
• 分页尽量用OFFSET/FETCH替代你示例里的TOP+OFFSET写法，这是SQL Server 2012及以后的标准写法，更清晰规范。
• 如果过滤参数特别多，可以用表达式树封装过滤逻辑，减少重复的if-else代码，让代码更简洁。

总结一下，优先选EF Core LINQ方案，省心又安全；如果必须用存储过程，就用参数化的版本，绝对不要手动拼接SQL！

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