如何在ASP中进行所有字段的搜索.净Api

Question

我有这个Get方法:

[HttpGet]
public async Task<ActionResult<IEnumerable<Customer>>> GetCustomers(string s, int page, int page_size)
{
IQueryable<Customer> query = _context.Customers;
if (!string.IsNullOrEmpty(s))
{
var stringProperties = typeof(Customer).GetProperties();
query = query.Where(c => stringProperties.Any(prop => prop.GetValue(c, null).ToString().Contains(s)));
}
return await query.Skip((page - 1) * page_size).Take(page_size).ToListAsync();
}

但是我的搜索实现不起作用，我得到这个错误

.Where(m => __stringProperties_0 .Any(prop => prop.GetValue(obj: m, index: null).ToString().Contains(__8__locals1_s_1)))不能翻译

我该如何解决这个问题?

Answer 1

好吧，你在处理PropertyInfo.GetValue的基本错误。在SQL服务器端，您没有对象，因此无法以这种方式获取值。你能做的就是指示EF如何比较属性。

我已经创建了通用函数，这可能有助于创建这样的查询。它可以用于任何类型，而不仅仅是字符串。

也考虑使用扩展，如果可能的话。

public static IQueryable<Customer> FilterCustomers(this IQueryable<Customer> query, string s)
{
if (!string.IsNullOrEmpty(s))
{
query = query.FilterByProperties(s, (prop, value) => prop.Contains(value), true)
}
return query;
}
public static Task<List<T>> PaginateAsync<T>(this IQueryable<T> query, int page, int page_size)
{
return query.Skip((page - 1) * page_size).Take(page_size).ToListAsync();
}

[HttpGet]
public Task<List<Customer>> GetCustomers(string s, int page, int page_size)
{
var query = _context.Customers.FilterCustomers(s);
return query.PaginateAsync(page, page_size);
}

和实现:

public static class QueryableExtensions
{
public static Expression<Func<T, bool>> MakePropertiesPredicate<T, TValue>(Expression<Func<TValue, TValue, bool>> pattern, TValue searchValue, bool isOr)
{
var parameter = Expression.Parameter(typeof(T), "e");
var searchExpr = Expression.Constant(searchValue);
var predicateBody = typeof(T).GetProperties()
.Where(p => p.PropertyType == typeof(TValue))
.Select(p =>
ExpressionReplacer.GetBody(pattern, Expression.MakeMemberAccess(
parameter, p), searchExpr))
.Aggregate(isOr ? Expression.OrElse : Expression.AndAlso);
return Expression.Lambda<Func<T, bool>>(predicateBody, parameter);
}
public static IQueryable<T> FilterByProperties<T, TValue>(this IQueryable<T> query, TValue searchValue,
Expression<Func<TValue, TValue, bool>> pattern, bool isOr)
{
return query.Where(MakePropertiesPredicate<T, TValue>(pattern, searchValue, isOr));
}
class ExpressionReplacer : ExpressionVisitor
{
readonly IDictionary<Expression, Expression> _replaceMap;
public ExpressionReplacer(IDictionary<Expression, Expression> replaceMap)
{
_replaceMap = replaceMap ?? throw new ArgumentNullException(nameof(replaceMap));
}
public override Expression Visit(Expression node)
{
if (node != null && _replaceMap.TryGetValue(node, out var replacement))
return replacement;
return base.Visit(node);
}
public static Expression Replace(Expression expr, Expression toReplace, Expression toExpr)
{
return new ExpressionReplacer(new Dictionary<Expression, Expression> { { toReplace, toExpr } }).Visit(expr);
}
public static Expression Replace(Expression expr, IDictionary<Expression, Expression> replaceMap)
{
return new ExpressionReplacer(replaceMap).Visit(expr);
}
public static Expression GetBody(LambdaExpression lambda, params Expression[] toReplace)
{
if (lambda.Parameters.Count != toReplace.Length)
throw new InvalidOperationException();
return new ExpressionReplacer(Enumerable.Range(0, lambda.Parameters.Count)
.ToDictionary(i => (Expression)lambda.Parameters[i], i => toReplace[i])).Visit(lambda.Body);
}
}
}