Dynamic query for creating where predicate with inner Collection

.net c# dynamicquery entity-framework-6 linq

Question

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
5
4/12/2018 9:06:23 AM

Popular Answer

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
4/25/2018 3:35:50 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow