使用内部集合创建 where 谓词的动态查询 [英] Dynamic query for creating where predicate with inner Collection

问题描述

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

I am creating search capability for my MVC EF application. I am creating it using dynamic query. And following this method https://www.codeproject.com/Articles/493917/Dynamic-Querying-with-LINQ-to-Entities-and-Express

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

Its for creating predicate for bool and string fields of entity. Main entity in my app is 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 的申请人教育中归档的机构名称.申请人可以有一个或多个申请人教育对象.

I want to search i.e. with Name of institution which is filed in ApplicantEducations of type Institute. Applicant can have one or many ApplicantEducations objects.

以下是我的申请者教育的 EDMX 课程

Following is my ApplicantEducations's EDMX class

   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; }
}

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

And my Institute entity class is like this

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

  }

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

So User will search by specifying Name of institute and all applicant will get retrieved with education from that institute.

正如我上面提到的链接.下面以字符串字段谓词构建为例进行演示

As I mentioned above the link. Following for example is demonstrated for string field predicate building

     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) 中的简单字符串字段构建谓词.但是申请人也有申请人教育集合，所以我的问题是如何为 linq 的 where 子句(方法)创建一个动态查询(谓词)，这样当用户搜索机构名称时，所有申请人都将被检索到相同的教育.

the above code for building a predicate for simple string field contained in main Entity class (Applicant). But Applicant also has ApplicantEducation collection, so my question is how can I create a dynamic query (predicate) for where clause (method) of linq so when user search for institute name then all applicant will get retrieved with same education.

我的搜索条件如下，

  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; }



}

我有点迷茫，这怎么可能.

I am kind of lost how can this be possible.

谢谢

推荐答案

在您的案例中，我们需要的基本内容是使用 EF 的 Dynamic Query builder.即一个基本的匹配"方法，它包含 IQueryable 格式的数据、搜索词和过滤记录的属性.Match"方法是我们需要在代码中使用的方法.

The basic thing that we need here in your case is a Dynamic Query builder using EF. i.e a basic "Match" method that contains data as an IQueryable format, the search term and the properties via which it will filter the records. The "Match" method is the one that we need to use in our code.

 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方法，以便它可以接受需要搜索的参数.

To build up this expression method, we need a Compose method so that it can accept the parameters that requires to be searched.

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"扩展方法.顾名思义，此方法会将一个表达式的所有实例替换为另一个.

This will compose and return you a lambda expression, but to build up this method we require the "Replace" extension method. As the name suggests, this method will replace all instances of one expression to another.

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);
    }
}

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

Going back to the actual "Match" method, we need a predicate builder which will help us render the AND, OR queries associated with the search.

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

Hence, a predicate builder will look like :

 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 方法，该方法将根据您的要求运行.

Hence, what we need is a Match method that will then function per your requirement.

如果您需要任何进一步的帮助，请告诉我，具体取决于您的模型结构.

Do let me know if you need any further assistance on this, based on your model structure.

这篇关于使用内部集合创建 where 谓词的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！