c# - 使用传入的参数作为搜索字段使用 Entity Framework Web API 创建动态搜索查询

Question

您好，我想创建一个动态搜索查询，使用具有来自 URI 的值的实体作为搜索字段。

下面的代码有效，但我必须搜索的一些表有超过 200 个字段，我想创建一些东西来获取属性名称并允许我在上面搜索。

它也可以有多个搜索选项，例如姓氏、名字和出生日期

queryable = queryable.Where(x => x.<PROPERTY.NAME>.Contains(query.<PROPERTY.NAME>));

这可能吗？

到目前为止，这是我的代码。

public List<EMPLOYEE> Get([FromUri] EMPLOYEE query)
    {

        List<EMPLOYEE> emps = new List<EMPLOYEE>();
        var db = AuthHandler.Ent;
        var queryable = db.EMPLOYEES.AsExpandable();

        foreach (var prop in query.GetType().GetProperties())
        {

            if (prop.GetValue(query, null) != null)
            {

                switch (prop.Name)
                {
                    case "EMP_CREATIONDATE":
                        queryable = queryable.Where(x => x.EMP_CREATIONDATE.Equals(query.EMP_CREATIONDATE));
                        break;
                    case "EMP_SURNAME":
                         queryable = queryable.Where(x => x.EMP_SURNAME.Contains(query.EMP_SURNAME));
                        break;
                    case "EMP_GIVENNAMES":
                        queryable = queryable.Where(x => x.EMP_GIVENNAMES.Contains(query.EMP_GIVENNAMES));
                        break;
                }
                queryable = queryable.Where(x => x.EMP_SURNAME.Contains(query.EMP_SURNAME));

            }
        }
        emps = queryable.ToList();

        return emps;

    }

Answer 1

我将使用这样的谓词构建器

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

然后像这样使用它:

public IQueryable<Customer> GetCustomers(CustomerOrderSearchParameters parameters)
        {    
                context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
                var predicate = PredicateBuilder.True<Customer>();
                if (!string.IsNullOrEmpty(parameters.FirstName))
                {
                    predicate = predicate.And(x => x.FirstName.Contains(parameters.FirstName));
                }
                if (!string.IsNullOrEmpty(parameters.LastName))
                {
                    predicate = predicate.And(x => x.LastName.Contains(parameters.LastName));
                }
                if (!string.IsNullOrEmpty(parameters.Email))
                {
                    predicate = predicate.And(x => x.email.Contains(parameters.Email));
                }
                if (!string.IsNullOrEmpty(parameters.PhoneNumber))
                {
                    predicate = predicate.And(x => x.MobilePhone.Contains(parameters.PhoneNumber) || x.HomePhone.Contains(parameters.PhoneNumber));
                }
                if (parameters.BrandID != null)
                {
                    predicate = predicate.And(x => x.Logins.Where(l => l.BrandID == parameters.BrandID).Any());
                }
                if (parameters.ShowNoOrders == true)
                {
                    predicate = predicate.And(x => x.Orders.Where(o => o.CustomerID != x.CustomerID).Any());
                }                
                return context.Customers.AsExpandable().Where(predicate);           
        }

此外，CustomerOrderSearchParameters 只是一个简单参数列表(等同于您将从查询中获得的参数)