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.

I've done similar things to create optional query criteria. For you error, my first guess is the ToLower() is where your object error is coming from. Try removing the criteria for the h.ProjectPlanName in your where clause and see if your error goes away. If the error doesn't occur then you know where to focus. If this is the case then you can perform additional checks for null in that condition before you call ToLower(). — quaabaam
– quaabaam, Commented Aug 4, 2022 at 23:28
Also, if you are not familiar with LINQPad you should be, it's an excellent tool for development and testing of LINQ queries. — quaabaam
– quaabaam, Commented Aug 4, 2022 at 23:35
I checked out LinqPad and it looks pretty cool. I was hoping I could just copy and paste my query from my code to test it, but I get an error about the context not existing. Is there any way to make it recognize the context? — Andrew Casey
– Andrew Casey, Commented Aug 8, 2022 at 16:30
To use your context you can start here, Using LINQPad with Entity Framework. This will explain how to reference your .DLL with your EF models, which will allow you to use your context. Note, the name you create for your connection will be used where you have context in your code. This would default to the name of the type in the referenced DLL, you can change this by setting a value in the Name for this connection option. — quaabaam
– quaabaam, Commented Aug 8, 2022 at 19:36

Svyatoslav Danyliv · Accepted Answer · 2022-08-08 16:08:05Z

2

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

edited Aug 8, 2022 at 16:08

answered Aug 5, 2022 at 8:09

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ivan Stoev Over a year ago

Doubt the extension will work inside query expression tree. For top level queryables, yes, sure, but inside...

Svyatoslav Danyliv Over a year ago

You are right, EF Core cannot translate this. Will correct answer.

Andrew Casey Over a year ago

Thanks for that, but I have a question. Includes are not the same as joins, correct? The roleId, disciplineId, and resourceId, are all in the planactivityline item. Are you saying that even with just includes, EF will be able to read those values at that level? If so, that's awesome! I'll give this a try.

Svyatoslav Danyliv Over a year ago

It should work. It is usual Navigation properties usage.

Andrew Casey Over a year ago

I just tried this and literally copied and pasted it. I get an error about "r" does not exist in the current context. The error appears in the bottom 3 where clauses.

|

JHBonarius · Accepted Answer · 2022-08-05 09:01:03Z

0

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

edited Aug 5, 2022 at 9:01

answered Aug 5, 2022 at 8:16

JHBonarius

11.5k3 gold badges28 silver badges53 bronze badges

1 Comment

Andrew Casey Over a year ago

Sorry for the late reply to this. I tried this and ran into a problem. The problem is, that if there are multiple revisions, which is very common, I get too many headers back. For example, I have 6 projects on my local database, but when I filter on resource, I get 11 headers back.

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related