c# - 使用内部集合创建 where 谓词的动态查询

Question

我正在为我的 MVC EF 应用程序创建搜索功能。我正在使用动态查询创建它。并按照此方法https://www.codeproject.com/Articles/493917/Dynamic-Querying-with-LINQ-to-Entities-and-Express

它用于为实体的 bool 和 string 字段创建谓词。我应用中的主要实体是 Applicant

EDMX 申请人正在关注

     public partial class Applicant
    {

      public Applicant()
       {
         this.ApplicantEducations = new HashSet<ApplicantEducation>();
         this.ApplicantSkills = new HashSet<ApplicantSkill>();
         this.Applications = new HashSet<Application>();
         this.Experiences = new HashSet<Experience>();
        }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public byte[] CV_Upload1 { get; set; }
    public string CV_Upload2 { get; set; }
    public string email { get; set; }
    public string password { get; set; }
    public Nullable<System.DateTime> DOB { get; set; }

   virtual ICollection<ApplicantEducation> ApplicantEducations { get; set; }
   virtual ICollection<ApplicantSkill> ApplicantSkills { get; set; }
   virtual ICollection<Application> Applications { get; set; }
   virtual ICollection<Experience> Experiences { get; set; }
}

我想搜索，即使用 Institute 类型的 ApplicantEducations 中提交的机构名称进行搜索。 Applicant 可以有一个或多个 ApplicantEducations 对象。

以下是我的 ApplicantEducations 的 EDMX 类

   public partial class ApplicantEducation
{
    public int id { get; set; }
    public Nullable<int> ApplicantId { get; set; }
    public Nullable<int> InstituteId { get; set; }
    public Nullable<int> EducationLevelId { get; set; }
    public Nullable<bool> IsComplete { get; set; }
    public Nullable<System.DateTime> DateStart { get; set; }
    public Nullable<System.DateTime> DateEnd { get; set; }
    public Nullable<short> GPA { get; set; }

    public virtual EducationLevel EducationLevel { get; set; }
    public virtual Institute Institute { get; set; }
    public virtual Applicant Applicant { get; set; }
}

而我的研究所实体类是这样的

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

  }

因此，用户将通过指定机构名称进行搜索，所有申请人都将获得该机构的教育背景。

正如我在上面提到的链接。以下示例演示了字符串字段谓词构建

     private static Expression<Func<TDbType, bool>> ApplyStringCriterion<TDbType,
        TSearchCriteria>(TSearchCriteria searchCriteria, PropertyInfo searchCriterionPropertyInfo,
        Type dbType, MemberInfo dbFieldMemberInfo, Expression<Func<TDbType, bool>> predicate)
    {
        // Check if a search criterion was provided
        var searchString = searchCriterionPropertyInfo.GetValue(searchCriteria) as string;
        if (string.IsNullOrWhiteSpace(searchString))
        {
            return predicate;
        }
        // Then "and" it to the predicate.
        // e.g. predicate = predicate.And(x => x.firstName.Contains(searchCriterion.FirstName)); ...
        // Create an "x" as TDbType
        var dbTypeParameter = Expression.Parameter(dbType, @"x");
        // Get at x.firstName
        var dbFieldMember = Expression.MakeMemberAccess(dbTypeParameter, dbFieldMemberInfo);
        // Create the criterion as a constant
        var criterionConstant = new Expression[] { Expression.Constant(searchString) };
        // Create the MethodCallExpression like x.firstName.Contains(criterion)
        var containsCall = Expression.Call(dbFieldMember, StringContainsMethod, criterionConstant);
        // Create a lambda like x => x.firstName.Contains(criterion)
        var lambda = Expression.Lambda(containsCall, dbTypeParameter) as Expression<Func<TDbType, bool>>;
        // Apply!
        return predicate.And(lambda);
    }

上面的代码用于为主实体类(申请人)中包含的简单字符串字段构建谓词。但是 Applicant 也有 ApplicantEducation 集合，所以我的问题是如何为 linq 的 where 子句(方法)创建一个动态查询(谓词)，这样当用户搜索学院名称时，所有申请人都会被检索到相同的教育。

我的搜索条件如下，

  public class SearchCriteriaVM
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime? DOB { get; set; }     
    public string Description { get; set; }

    public ICollection<Models.ApplicantEducationVM> ApplicantEducations { get; set; }
    public ICollection<Models.ExperienceVM> Experiences { get; set; }
    public ICollection<Models.ApplicantSkillsVM> ApplicantSkills { get; set; }

    public ICollection<Models.ApplicationsVM> Applications { get; set; }



}

我有点迷茫这怎么可能。

谢谢

Answer 1

在您的案例中，我们需要的基本内容是使用 EF 的动态查询构建器。即一个基本的“匹配”方法，它包含 IQueryable 格式的数据、搜索词和用于过滤记录的属性。 “Match”方法是我们需要在代码中使用的方法。

 public static IQueryable<T> Match<T>(
    IQueryable<T> data,
    string searchTerm,
    IEnumerable<Expression<Func<T, string>>> filterProperties)
{
    var predicates = filterProperties.Select(selector =>
            selector.Compose(value => 
                value != null && value.Contains(searchTerm)));
    var filter = predicates.Aggregate(
        PredicateBuilder.False<T>(),
        (aggregate, next) => aggregate.Or(next));
    return data.Where(filter);
}

要构建这个表达式方法，我们需要一个Compose方法，以便它可以接受需要搜索的参数。

public static Expression<Func<TFirstParam, TResult>>
    Compose<TFirstParam, TIntermediate, TResult>(
    this Expression<Func<TFirstParam, TIntermediate>> first,
    Expression<Func<TIntermediate, TResult>> second)
{
    var param = Expression.Parameter(typeof(TFirstParam), "param");

    var newFirst = first.Body.Replace(first.Parameters[0], param);
    var newSecond = second.Body.Replace(second.Parameters[0], newFirst);

    return Expression.Lambda<Func<TFirstParam, TResult>>(newSecond, param);
}

这将组成并返回一个 lambda 表达式，但要构建此方法，我们需要“Replace”扩展方法。顾名思义，此方法会将一个表达式的所有实例替换为另一个表达式。

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

回到实际的“Match”方法，我们需要一个谓词构建器来帮助我们呈现与搜索相关的 AND、OR 查询。

因此，谓词构建器将如下所示:

 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 secondBody = expr2.Body.Replace(
            expr2.Parameters[0], expr1.Parameters[0]);
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, secondBody), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var secondBody = expr2.Body.Replace(
            expr2.Parameters[0], expr1.Parameters[0]);
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, secondBody), expr1.Parameters);
    }
}

因此，我们需要的是一个 Match 方法，它可以根据您的要求运行。

如果您需要任何进一步的帮助，请根据您的模型结构告诉我。