How to filter nested IQueryable <T> elements?

Question

I am creating an expression of type Expression<Func<Project, bool>> that returns the correct IQueryable<Project> from the database. IQueryable<Project> has a nested set of SubProjects that I would like to filter as well. It looks something like this.

Can this be done with a single call to the database?

For example:

 Expression<Func<Project, bool>> projectFilter = FilterEnabled(); projectFilter = projectFilter.And(GetProjectsByOrganization()); var projectData = GetProjectsAsQueryable(projectFilter); //returns correct projects 

This is what I would like to do:

 Expression<Func<Project, bool>> projectFilter = FilterEnabled(); projectFilter = projectFilter.And(GetProjectsByOrganization()) .And(GetSubProjectsByStartDate()); var projectData = GetProjectsAsQueryable(projectFilter); //returns correct projects and the filtered sub projects by start date 

GetProjectsByOrganization as follows

 public Expression<Func<Project, bool>> GetProjectByOrganization() { var organizationIDs = new List<Guid>(); if (FilterCriteria.OrganiazaitonId != null) organizationIDs = OrganizationRepository.GetParentAndChildrenOrganizationIds(FilterCriteria.OrganiazaitonId.Value).ToList(); //... return prj => FilterCriteria.OrganiazaitonId == null || organizationIDs.Contains(prj.OrganizationID.Value); } 

How to add Expression<Func<SubProject, bool>> to the filter? If not, what alternatives do I have?

Answer 1

You can use Expression.AndAlso to combine all 3 expressions into a new one. With Expression.PropertyOrField you can pass a SubProject instead of your Project as a parameter:

  static Expression<Func<Project, bool>> CombineFilterExpression( Expression<Func<Project, bool>> firstProjectFilter, Expression<Func<Project, bool>> secondProjectFilter, Expression<Func<SubProject, bool>> subProjectFilter ) { //Create Project Parameter var param = Expression.Parameter(typeof(Project)); //Create && Expression var body = Expression.AndAlso( Expression.Invoke(firstProjectFilter, param), Expression.AndAlso( //Create second && Expression Expression.Invoke(secondProjectFilter, param), //Pass SubProject instead of Project Expression.Invoke(subProjectFilter, Expression.PropertyOrField(param, nameof(Project.SubProject))) ) ); //Make Lambda with Project parameter return Expression.Lambda<Func<Project, bool>>(body, param); }