Dynamic query for creating where predicate with in

问题:

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

Its for creating predicate for bool and string fields of entity. Main entity in my app is Applicant

EDMX Applicant is following

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

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.

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

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.

My search criteria is given below,

  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.

Thanks

回答1:

You can use the following approach in order to create a Dynamic Where clause in Lambda expression:

public ActionResult GetRecords(int? classId, string name, bool isAll = false)
{
    var allRecords = repository.Students;

    if (!isAll)
    {
        //Retrieve active records only
        allRecords = allRecords.Where(m => m.StatusId == 1);
    }
    if (!string.IsNullOrEmpty(name))
    {
        allRecords = allRecords.Where(m => m.Name.StartsWith(name));
    }
    if (classId.HasValue)
    {
        allRecords = allRecords.Where(m => m.ClassId == classId);
    }
    // other stuff
}

Similarly, the following approach can be applied in order to retrieve only the records starts with the “query” parameter value and retrieving all records if “query” parameter value is null:

IQueryable<StudentViewModel> students = repository.Students.Select(m => 
    new StudentViewModel
{
    Id = m.Id,
    Name = m.Name + " " + m.Surname
});
if (!string.IsNullOrEmpty(query))
{
    students = students.Where(m => m.Name.StartsWith(query));
}

Or another way with “poor performance”:

.Where(m => string.IsNullOrEmpty(query) || m.Name.StartsWith(query));

Hope this helps...

回答2:

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

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

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

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

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.