如何通过EF的GetAsync方法实现多条件过滤与排序？

你之前在客户端处理过滤和排序的方式会拉取全量数据到内存后再处理，性能低下，正确的做法是将过滤和排序逻辑推给数据库执行，利用EF的表达式树生成对应的SQL语句。下面是基于你提供的GetAsync方法的实现步骤：

一、准备工作：安装LinqKit简化表达式拼接 #

要方便地拼接多条件过滤表达式，推荐使用LinqKit工具包，通过NuGet安装LinqKit.Microsoft.EntityFrameworkCore即可。

二、构建多条件过滤表达式 #

GetAsync方法接收的filter参数是Expression<Func<T, bool>>类型，它会被EF转换成SQL的WHERE条件。我们可以根据请求参数动态拼接过滤条件：

// 初始化过滤条件：默认匹配所有数据
var filter = PredicateBuilder.New<BlackList>(true);

// 从请求中提取过滤参数
string? lastName = request.LastNameS;
string? firstName = request.FirstNameS;
string? middleName = request.MiddleNameS;
string? phone = request.PhoneNameS;
DateTime? createDate = request.CreateDate; // 假设请求包含日期参数

// 按参数是否有效拼接过滤条件
if (!string.IsNullOrWhiteSpace(lastName))
{
    filter = filter.And(x => x.LastName == lastName);
}

if (!string.IsNullOrWhiteSpace(firstName))
{
    filter = filter.And(x => x.FirstName == firstName);
}

if (!string.IsNullOrWhiteSpace(middleName))
{
    filter = filter.And(x => x.MiddleName == middleName);
}

if (!string.IsNullOrWhiteSpace(phone))
{
    filter = filter.And(x => x.PhoneNumber == phone);
}

if (createDate.HasValue)
{
    // 如需精确匹配日期则用==，如需匹配日期范围可调整为x.CreateDate.Date == createDate.Value.Date
    filter = filter.And(x => x.CreateDate == createDate.Value);
}

三、构建排序逻辑 #

GetAsync的sort参数是Func<IQueryable<T>, IOrderedQueryable<T>>类型，它会被EF转换成SQL的ORDER BY语句。根据请求的排序字段动态生成：

Func<IQueryable<BlackList>, IOrderedQueryable<BlackList>> sort = request.SortBy switch
{
    "LastName" => query => query.OrderBy(x => x.LastName),
    "LastNameDesc" => query => query.OrderByDescending(x => x.LastName),
    "FirstName" => query => query.OrderBy(x => x.FirstName),
    "FirstNameDesc" => query => query.OrderByDescending(x => x.FirstName),
    "CreateDate" => query => query.OrderBy(x => x.CreateDate),
    "CreateDateDesc" => query => query.OrderByDescending(x => x.CreateDate),
    // 可添加更多排序字段的分支
    _ => query => query.OrderByDescending(x => x.CreateDate) // 默认排序规则
};

四、整合调用GetAsync并处理结果 #

将构建好的过滤和排序参数传入GetAsync，并处理返回结果：

public async Task<IEnumerable<BlackList>> Handle(GetBlackListRequest request, CancellationToken cancellationToken)
{
    // 构建过滤表达式
    var filter = PredicateBuilder.New<BlackList>(true);

    string? lastName = request.LastNameS;
    string? firstName = request.FirstNameS;
    string? middleName = request.MiddleNameS;
    string? phone = request.PhoneNameS;
    DateTime? createDate = request.CreateDate;

    if (!string.IsNullOrWhiteSpace(lastName))
    {
        filter = filter.And(x => x.LastName == lastName);
    }

    if (!string.IsNullOrWhiteSpace(firstName))
    {
        filter = filter.And(x => x.FirstName == firstName);
    }

    if (!string.IsNullOrWhiteSpace(middleName))
    {
        filter = filter.And(x => x.MiddleName == middleName);
    }

    if (!string.IsNullOrWhiteSpace(phone))
    {
        filter = filter.And(x => x.PhoneNumber == phone);
    }

    if (createDate.HasValue)
    {
        filter = filter.And(x => x.CreateDate == createDate.Value);
    }

    // 构建排序委托
    Func<IQueryable<BlackList>, IOrderedQueryable<BlackList>> sort = request.SortBy switch
    {
        "LastName" => query => query.OrderBy(x => x.LastName),
        "LastNameDesc" => query => query.OrderByDescending(x => x.LastName),
        "FirstName" => query => query.OrderBy(x => x.FirstName),
        "FirstNameDesc" => query => query.OrderByDescending(x => x.FirstName),
        "CreateDate" => query => query.OrderBy(x => x.CreateDate),
        "CreateDateDesc" => query => query.OrderByDescending(x => x.CreateDate),
        _ => query => query.OrderByDescending(x => x.CreateDate)
    };

    // 调用Repository方法
    var result = await _readonlyRepository.GetAsync(cancellationToken, filter, sort);

    // 处理自定义Result类型的返回值
    if (result.IsSuccess)
    {
        // 如需投影到DTO，可在此处对result.Value进行Select操作（确保是IQueryable时执行数据库端投影）
        return result.Value;
    }
    else
    {
        // 根据业务需求处理失败场景，比如抛出异常或返回空集合
        throw new InvalidOperationException(result.ErrorMessage);
    }
}

关键注意点 #

• 避免客户端处理：所有过滤和排序逻辑都通过表达式传递给EF，由PgSql执行，仅返回符合条件的数据，大幅提升性能。
• Expression vs Func：必须使用Expression<Func<T, bool>>作为过滤条件，它会被EF解析为SQL；而Func<T, bool>是内存中执行的委托，无法转换成SQL。
• 投影优化：如果只需要部分字段，优先在IQueryable上调用Select生成DTO，这样投影操作会在数据库端执行，减少数据传输量。

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