How can I use optional parameters in a query with multiple joins, using Entity Framework?

Question

I've looked at several possible solutions to this problem, and the ones I have tried do not seem to work. One solution was to use if statements for the optional filters, which doesn't work because I have multiple joins and the where clause is in the last join.

The optional parameters are: roleId, disciplineId, resourceId, and projectName.

try
{
    IQueryable<ProjectPlanHeader> bob = 
        (
            from h in context.ProjectPlanHeaders
            join r in context.ProjectPlanRevisions on h.ProjectPlanHeaderId equals r.ProjectPlanHeaderId
            join a in context.PlanActivityLineItems on r.PlanRevisionId equals a.PlanRevisionId
            where ((roleId == null || a.RequiredRoleId == roleId) && 
                (disciplineId == null || a.DisciplineId == disciplineId) && 
                (resourceId == null || a.ActualResourceId == resourceId) && 
                (h.ProjectPlanName.ToLower().Contains(projectName.ToLower()) || projectName == String.Empty))
            select h
        )
        .Include(x => x.ProjectPlanRevisions)
            .ThenInclude(y => y.PlanActivityLineItem)
                .ThenInclude(z => z.PlannedHours)
        .Include(x => x.ActualPlanRevisions)
            .ThenInclude(y => y.ActualPlanActivities)
                .ThenInclude(z => z.ActualHours);

    var john = bob.ToList();
    return bob;
}
catch (Exception ex)
{
    return null;
}

I added the try/catch so I could see what was happening, as it was silently failing. What I found was a "Object not set to an instance of an object". That's never helpful, because I don't know what object it's talking about. Can someone please show me how to do this the right way?

UPDATE: Thanks for the responses I got, but unfortunately they don't work. The problem is that I end up getting multiple headers back when I filter. This happens because there are multiple revisions for each header, and I really only need the max rev. I tried changing the initial query so that only the max rev was included, and that still did not help. There does not appear to be a solution for this issue, so I will have to do it another way.

Answer 1

Rewrite query to do not use explicit joins, because you have navigation properties. Also because of JOINS you have duplicated records, you will discover it later.

var query = context.ProjectPlanHeaders
    .Include(x => x.ProjectPlanRevisions)
        .ThenInclude(y => y.PlanActivityLineItem)
            .ThenInclude(z => z.PlannedHours)
    .Include(x => x.ActualPlanRevisions)
        .ThenInclude(y => y.ActualPlanActivities)
            .ThenInclude(z => z.ActualHours)
    .AsQueryable();

if (!string.IsNullOrEmpty(projectName))
{
    // here we can combine query
    query = query
        .Where(h => h.ProjectPlanName.ToLower().Contains(projectName.ToLower()));
}

// check that we have to apply filter on collection
if (roleId != null || disciplineId != null || resourceId != null)
{
    // here we have to do filtering as in original query
    query = query
        .Where(h => h.ProjectPlanRevisions
            .Where(r => roleId == null || r.PlanActivityLineItem.RequiredRoleId == roleId)
            .Where(r => disciplineId == null || r.PlanActivityLineItem.DisciplineId == disciplineId)
            .Where(r => resourceId == null || r.PlanActivityLineItem.ActualResourceId == resourceId)
            .Any()
        );
}

var result = query.ToList();

Answer 2

Let me clarify my comment with an example:

So: An IQueryable is used to build up an expression tree. They are not evaluated until enummerated (e.g. ToList or FirstOrDefault). I.e. you can conditional add Where and Includes with little to no cost before ennumerating`

Thus you could do this,

IQueryable<ProjectPlanHeader> bob = 
    context.ProjectPlanHeader
        .Include(x => x.ProjectPlanRevisions)
            .ThenInclude(y => y.PlanActivityLineItem);

if (roleId != null) {
    bob =
        from h in bob
        join r in context.ProjectPlanRevisions on h.Id equals r.ProjectPlanHeaderId
        join a in context.PlanActivityLineItems on r.Id equals a.ProjectPlanRevisionId
        where a.RequiredRoleId == roleId
        select h;
}

// same for the rest.

var john = bob.ToList();

writing the chained filer is not easiest, but it works