如何利用反射按属性动态查询IQueryable集合？

我需要实现一个以DTO为参数的搜索方法，要求根据DTO的属性动态执行Where过滤逻辑。之前用反射实现时必须显式转换属性类型，现在希望借助Dynamic Linq Library来实现无需提前知晓属性类型的动态过滤，以下是我编写的代码：

public async Task<ServiceResponceWithData<List<SearchResponseDTO>>> SearchAsync(SearchDTO model)
{
    var users = from user in _userManeger.Users
                join ur in _context.UserRoles
                       on user.Id equals ur.UserId
                select new SearchResponseDTO
                {
                    UserName = user.UserName,
                    Email = user.Email,
                    PhoneNumber = user.PhoneNumber,
                    FirstName = user.FirstName,
                    LastName = user.LastName,
                    Roles = _roleManager.Roles
                            .Where(c => c.Id == ur.RoleId)
                            .Select(c => c.Name)
                            .ToList()
                };

    var startResult = users;

    var props = typeof(SearchDTO).GetProperties();
    foreach (var prop in props)
    {
        if(prop.Name != "Role")
        {
            users = FilterWithWhereByStringTypeProperty(users, prop, model);
        }
        else
        {
            if (model.Role != null)
            {
                var results = users.Where(c => c.Roles.Any(r => r == model.Role));
                users = results.Any() ? results : users;
            }
        }
    }

    var endResultList =  startResult == users ? new List<SearchResponseDTO>() :await  users.ToListAsync();

    return new ServiceResponceWithData<List<SearchResponseDTO>>() { Data = endResultList, Success = true };
}

private IQueryable<SearchResponseDTO> FilterWithWhereByStringTypeProperty(IQueryable<SearchResponseDTO> collection,
                                            PropertyInfo property,SearchDTO model )
{
    var propertyName = property.Name;
    var modelPropVal = property.GetValue(model);

    if (modelPropVal == null) return collection;

    string val = (string)modelPropVal;
    string condition = String.Format("{0} == \"{1}\"", propertyName, val);
    var fillteredColl = collection.Where(condition);
    return fillteredColl.Any() ? fillteredColl : collection;
}

几点优化建议： #

• 避免注入风险：当前直接用String.Format拼接过滤条件存在安全隐患，建议改用Dynamic Linq的参数化查询方式，既安全又简洁：

  // 替换原条件拼接代码
  var fillteredColl = collection.Where($"{propertyName} == @0", val);
  

• Role过滤逻辑优化：当前Roles是通过ToList()加载到内存的集合，过滤逻辑是在内存中执行。如果要将过滤下推到数据库层面，需要调整查询结构，避免提前加载角色数据。
• 结果判断逻辑修正：startResult == users的引用判断不合理——若所有过滤条件都未触发，users的引用不会改变，此时返回空列表不符合预期，应直接执行查询返回全部结果。

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