构建从 linq 到 ef core 的动态 where 查询

Question

我的模型如下所示

public class Country
{
  public int Id {get; set;}
  public string Name {get; set;}
}

public class User
{
 public int Id{get; set;}
 public string FirstName {get; set;}
 public string LastName {get; set;}
 public string Email {get; set;}
}

public class Company
{
  public int Id{get; set;}
  public string Name {get;set;}
  public string City {get; set;}
  public string Address1 {get; set;}
  public string Address2 {get; set;}
  [ForeignKey("Country")]
  public int CountryId{get; set;}

  public Country Country{get; set;}
  public ICollection<CompanyUsers> CompanyUsers {get; set;}
}

public class CompanyUsers
{
 public int Id{get; set;}
 [ForeignKye("Company")]
 public int CompanyId {get; set;}
 [ForeignKye("User")]
 public int UserId {get; set;}

 public Country Country{get; set;}
 public User User{get; set;}
}

我希望让用户能够通过国家、公司或用户类中定义的任何属性搜索公司记录除了 Id 属性

我尝试查看这个SO讨论但它不处理导航属性。有人可以帮助我构建动态 where 子句，包括导航属性中的属性。

通过这个实现我只能做如下

myContext.CompanyEntity
.Where(FilterLinq<CompanyEntity>
.GetWherePredicate(searchParams))
.ToList();

public class SearchField
{
  public string Key { get; set; }
  public string Value { get; set; }
}

Answer 1

从您指出的答案中扩展这个想法并不那么复杂。

您需要更改假设，以便 SerchField 中的属性名称可以包含属性路径的定义，例如 Company.City 或 Company.Country.Name 以及属性 FirstName。并且您需要处理它：

，而不仅仅是像这样简单的属性

Expression columnNameProperty = Expression.Property(pe, fieldItem.Name);

因此，您需要处理属性链

Expression columnNameProperty = GetPropertyExpression(pe, fieldItem.Name);


Expression GetPropertyExpression(Expression pe, string chain){
    var properties = chain.Split('.');
    foreach(var property in properties)
          pe = Expression.Property(pe, property);

    return pe;
}

：基本上这段代码的作用是在创建属性链的每个循环上将属性应用于之前修改的 pe 变量。实体框架将处理它并创建适当的连接。这仅适用于单一关系，不适用于集合。

所以该答案的修改后的代码如下所示：

public class FilterLinq<T>
{
    Expression GetPropertyExpression(Expression pe, string chain)
    {
        var properties = chain.Split('.'); 
        foreach(var property in properties)
           pe = Expression.Property(pe, property);

        return pe;
    }

    public static Expression<Func<T, Boolean>> GetWherePredicate(params SearchField[] SearchFieldList)
    {

        //the 'IN' parameter for expression ie T=> condition
        ParameterExpression pe = Expression.Parameter(typeof(T), typeof(T).Name);

        //combine them with and 1=1 Like no expression
        Expression combined = null;

        if (SearchFieldList != null)
        {
            foreach (var fieldItem in SearchFieldList)
            {
                //Expression for accessing Fields name property
                Expression columnNameProperty = GetPropertyExpression(pe, fieldItem.Name);

                //the name constant to match 
                Expression columnValue = Expression.Constant(fieldItem.Value);

                //the first expression: PatientantLastName = ?
                Expression e1 = Expression.Equal(columnNameProperty, columnValue);

                if (combined == null)
                {
                    combined = e1;
                }
                else
                {
                    combined = Expression.And(combined, e1);
                }
            }
        }

        //create and return the predicate
        return Expression.Lambda<Func<T, Boolean>>(combined, new ParameterExpression[] { pe });
    }

}